Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
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
Highlighted

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

Highlighted

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?

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

Highlighted

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

Highlighted
Contributor
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?

Highlighted

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

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