Unable to use variable as a dimension within an aggregation function

[anubhavverma1]

Hi

Can you guys help me with an issue I need to get resolved at the earliest.

I am using a variable as an aggregating dimension within a rangesum function. 

The code is: 

sum(aggr(
rangesum(
above(
aggr(sum(Spend),
$(vMaxMonthYearEx))-
aggr(sum(Spend),
$(vPaidMonthYearEx))
,0,
if(rowno()>count(total distinct 
$(vPaidMonthYear)),
null(),
rowno()))),
$(vMaxMonthYearEx)))

where vPaidMonthYear and vMaxMonthYear are the two variables that take values of dimensions based on various buttons. eg

if vSwitch=1 then vPaidMonthYear equals FieldA and if vSwitch=0 then vPaidMonthYear equals FieldB.

However I am unable to get any value using this.

I have attached the relevant file. Please refer to the Spend Measure.

Kindly Help.

Thanks

Anubhav

[sunny_talwar]

Can you try this

Sum(Aggr(
RangeSum(
Above(
Aggr(sum(Spend),
$(=$(vMaxMonthYearEx))) -
Aggr(Sum(Spend),
$(=$(vPaidMonthYearEx)))
,0,
If(RowNo() > Count(TOTAL DISTINCT  
$(vPaidMonthYear)),
Null(),
RowNo()))),
$(vMaxMonthYearEx)))

[sunny_talwar]

Or this

Sum(Aggr(
RangeSum(
Above(
Aggr(sum(Spend),
$(=vMaxMonthYearEx)) -
Aggr(Sum(Spend),
$(=vPaidMonthYearEx))
,0,
If(RowNo() > Count(TOTAL DISTINCT  
$(vPaidMonthYear)),
Null(),
RowNo()))),
$(vMaxMonthYearEx)))

[anubhavverma1]

Thanks Sunny.

I have tried both these approaches but none of these seem to work.

Thanks

[sunny_talwar]

Would you be able to share a sample where we can see the issue?

[anubhavverma1]

I have attached the sample. Refer to the Spend Measure in the table.

Thanks

[sunny_talwar]

What is wrong in this table? The numbers seems to be populated? What is not correct?

[anubhavverma1]

The code I had shared is being used to populate the values in the first two dimensions created using valuelist().

Impact on ME Balance and Change in value paid last month is not getting populated as the aggreagtion being used in the rangesum function is not being evaluated correctly or is there something else?

[sunny_talwar]

This might be an issue related to AGGR() and Synthetic Dimensions