Set Analysis Comparison with Variables

[#1]

Hi everyone,

I am attempting to use the following expression in a measure:

sum({<Month = {'$(vCurrentMonth)'}>}IntakeBU)

-

sum({<Month = {'>= $(vCurrentMonth)'}>}IntakeValue)

The first set statement before the minus sign works fine. The one after it always outputs to zero.

The value of vCurrent month is month(today()).

I have verified that with a sum(IntakeValue) is far greater than 0 with the appropriate months selected. Can anyone explain why this doe not work or recommend a work-around that does not involve hard-coding the values?

Thanks

[Gysbert_Wassenaar · Accepted solution]

Try this:

change vCurrentMonth to =num(month(today()))

change your expression to:

sum({<Month = {'>=$(vCurrentMonth)<=$(vCurrentMonth)'}>}IntakeBU)

-

sum({<Month = {'>= $(vCurrentMonth)'}>}IntakeValue)

The cause is that $(vCurrentMonth) returns a string. The result is that you compare a numeric month value (because of the >= operator) with a string value. By changing the variable so that it return a number it's possible to use the >= and <= operators. To make the first part of the chart expression work I changed the modifier so it works with the number value of the variable.

[#3]

Ahh that explains why the first expression worked and the second did not. Thanks very much for your prompt and concise response. Some of your previous posts have been very helpful, so thanks for that as well.