DECLARE error

vthiruv2 · April 26, 2012 3:10PM

The following formula broke after I upgraded to 2.7-beta b326.
DECLARE(
curr_month , DATETOTEXT(NOW, "MMMM"),
next_month , DATETOTEXT(DATEADD(TODAY,1,"month") , "MMMM"),
val, SUBSET_SUM([Value], SUBSET2("Sub-Dept", "Month", [Sub-Dept], curr_month )),
IF([Month]= curr_month, (-1 * ( 0.4 * val )),
[Month]= next_month, (-1 * ( 0.6 * val )),
0)
)

It looks like I cannot use the variable "curr_month" in the formula for the declaration of another variable called "val" anymore.

Is there a workaround for this?

vthiruv2 · April 26, 2012 3:14PM

To answer my own workaround question, the following works:

DECLARE(
curr_month , DATETOTEXT(NOW, "MMMM"),
next_month , DATETOTEXT(DATEADD(TODAY,1,"month") , "MMMM"),
val, SUBSET_SUM([Value], SUBSET2("Sub-Dept", "Month", [Sub-Dept], DATETOTEXT(NOW, "MMMM") )),
IF([Month]= curr_month, (-1 * ( 0.4 * val )),
[Month]= next_month, (-1 * ( 0.6 * val )),
0)
)

steve · April 27, 2012 1:54AM

Vijay,


DECLARE(

   curr_month, DATETOTEXT(NOW, "MMMM"), 

   next_month , DATETOTEXT(DATEADD(TODAY,1,"month") , "MMMM"),

   val, SUBSET_SUM([Value], SUBSET2("Sub-Dept", "Month", [Sub-Dept], curr_month )), 

   IF([Month]= curr_month, (-1 * ( 0.4 * val )),
      [Month]= next_month, (-1 * ( 0.6 * val )),
      0)
)

You're right, this is no longer supported. It was an unintended and undocumented behaviour of earlier versions which was lost as part of a wider bug fix. DECLAREd arguments must only be referred to within the last argument.

Another solution would be to nest the DECLAREs:


DECLARE(

   curr_month, DATETOTEXT(NOW, "MMMM"), 

   next_month , DATETOTEXT(DATEADD(TODAY,1,"month") , "MMMM"),

   DECLARE(

      val, SUBSET_SUM([Value], SUBSET2("Sub-Dept", "Month", [Sub-Dept], curr_month )), 

      IF([Month]= curr_month, (-1 * ( 0.4 * val )),
         [Month]= next_month, (-1 * ( 0.6 * val )),
         0)
   )
)

Welcome!

Categories

Ideas Parade

Tagged