Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anubhavverma1
New Contributor

Unable to use variable as a dimension within an aggregation function

 

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

8 Replies

Re: Unable to use variable as a dimension within an aggregation function

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)))
Highlighted

Re: Unable to use variable as a dimension within an aggregation function

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
New Contributor

Re: Unable to use variable as a dimension within an aggregation function

Thanks Sunny.

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

Thanks

Re: Unable to use variable as a dimension within an aggregation function

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

anubhavverma1
New Contributor

Re: Unable to use variable as a dimension within an aggregation function

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

Thanks

Re: Unable to use variable as a dimension within an aggregation function

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

image.png

anubhavverma1
New Contributor

Re: Unable to use variable as a dimension within an aggregation function

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?

Re: Unable to use variable as a dimension within an aggregation function

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