Ignoring dimension selection in IF statement

[Ilze1]

Hi

I need to create a chart that, depending on the metric selected, either calculates a cumulative sum of values or just a straight sum of values over days in the month. The currently formula is:

if(Metric<>'a',
RangeSum(
Above(
sum({<[Date.autoCalendar.Month]={"$(=$(vMonth))"},[Date.autoCalendar.Year]={"$(=$(vYear))"}, Date>}Value)
,0,
RowNo()
)
),
Sum({<[Date.autoCalendar.Month]={"$(=$(vMonth))"},[Date.autoCalendar.Year]={"$(=$(vYear))"}, Date>}Value))

The problem in the above instance is that the first condition of the if statement evaluates to true only for the selected Date and as a result all other instances of days in the month will have a cumulative sum, when the requirement is that for all instances of this metric it should be a non-cumulative sum.

I have attached a qvf containing dummy data which replicates the problem and illustrates the problem with the evaluation of the if statement. The solution as far as I can see should be related to ignoring selections of date in the condition of the if statement, but for the life of me I cannot see how this can be achieved. I am aware that I could limit the value of the Metric in the set expression, this would imply that I need to create separate measures for each Metric.

[JordyWegman]

Hi Ilze,

You need set analysis in all your aggregation functions. I've added it to the RangeSum, try this:

if(Metric<>'a',
  RangeSum({<[Date.autoCalendar.Month]={"$(=$(vMonth))"},[Date.autoCalendar.Year]={"$(=$(vYear))"}, Date = >}
    Above(
      sum({<[Date.autoCalendar.Month]={"$(=$(vMonth))"},[Date.autoCalendar.Year]={"$(=$(vYear))"}, Date = >}Value)
,0,
RowNo()
)
),
Sum({<[Date.autoCalendar.Month]={"$(=$(vMonth))"},[Date.autoCalendar.Year]={"$(=$(vYear))"}, Date = >}Value))

Jordy

Climber

[Ilze1]

Hi Jordy

Thanks, but I just get an error when I use your proposed solution.