topic Re: SUM ISSUE FOR MULTIPLE YEARS in QlikView

SUM ISSUE FOR MULTIPLE YEARS

Tue, 20 Dec 2016 21:07:16 GMT

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.

Re: SUM ISSUE FOR MULTIPLE YEARS

vishsaggi — Tue, 20 Dec 2016 22:13:46 GMT

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

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

Re: SUM ISSUE FOR MULTIPLE YEARS

sunny_talwar — Tue, 20 Dec 2016 22:57:43 GMT

May be this?

=FirstSortedValue(Amounts, -TRANS_ID)

or this:

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

Re: SUM ISSUE FOR MULTIPLE YEARS

Wed, 21 Dec 2016 01:53:45 GMT

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)

Re: SUM ISSUE FOR MULTIPLE YEARS

sunny_talwar — Wed, 21 Dec 2016 01:56:31 GMT

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)

Re: SUM ISSUE FOR MULTIPLE YEARS

vishsaggi — Wed, 21 Dec 2016 02:57:41 GMT

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

LLike

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

Re: SUM ISSUE FOR MULTIPLE YEARS

sunny_talwar — Wed, 21 Dec 2016 10:45:16 GMT

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

Re: SUM ISSUE FOR MULTIPLE YEARS

vishsaggi — Wed, 21 Dec 2016 14:20:59 GMT

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