Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SUM ISSUE FOR MULTIPLE YEARS

Attached please find the qvw.  Here is a brief description of the issue.

I have a table with years 2016 and 2017, each year has a monthly snapshot Amounts.  I would  like to sum the Amounts for each  "Year"  but only for the maximum MONTH_ID. 

So in this case I would like to see Year 2016  Sales Amount ='1000000' and  Year 2017 Sales Amount ='1000000' also.

Using Qlik 11.2 SR13

Any suggestions. 

7 Replies
vishsaggi
Champion III
Champion III

You are already using dimension as Year.

Why dont you just use  Amounts as your expression.

What exactly is your requirement or trying to achieve in that table?

Expr: = Amounts

OR

Expr: = Max({< Year = { $(=Max(Year)-1), $(=Max(Year)) } >} Amounts)

sunny_talwar

May be this?

=FirstSortedValue(Amounts, -TRANS_ID)

or this:

=FirstSortedValue(Aggr(Sum(Amounts), TRANS_ID), -TRANS_ID)

Not applicable
Author

I tried using urs in my original App and it doesnt work,Below is my actual expresion

FirstSortedValue(Aggr(Sum({<Entity_Type={'O'}>} LIMITS), MONTH_ID),-MONTH_ID)

sunny_talwar

I won't know what exactly is going wrong, but one suggestion might be to use the set analysis for the firstsortedvalue() function also

FirstSortedValue({<Entity_Type={'O'}>} Aggr(Sum({<Entity_Type={'O'}>} LIMITS), MONTH_ID),-MONTH_ID)

vishsaggi
Champion III
Champion III

‌Sunny, can we use only() outside the aggr() get that specific monthID ? Just wondering?

LLike

firstsortedvalue(limits, -only(aggr(sum(), month_id))

sunny_talwar

I don't think this is a correct syntax....

vishsaggi
Champion III
Champion III

Sorry you are right. I dont know what i was thinking.