topic Re: How to sum the last value for all the members - before the selected year WITH Filter? in QlikView

How to sum the last value for all the members - before the selected year WITH Filter?

yanivvl0 — Wed, 30 Mar 2016 08:50:04 GMT

hi everybody ,

i had a previos question - see https://community.qlik.com/thread/207054?sr=inbox

responded beautifully by Gysbert Wassenaar ,

The thing is that there is a need to do further screening of the formula that produces the desired result: - after the calculation exists - we have to find occurrences of a particular status (say 90) only summarize them. You can see highlighted in yellow which from them - to summarize only those with a red X, Many thanks.

Re: How to sum the last value for all the members - before the selected year WITH Filter?

sunny_talwar — Wed, 30 Mar 2016 08:55:16 GMT

Are you trying to do this in a text box object or the table you need another column?

Re: How to sum the last value for all the members - before the selected year WITH Filter?

Gysbert_Wassenaar — Wed, 30 Mar 2016 08:55:21 GMT

Does this give you the result you want?

=sum({<P_NO_1=P({<STATUS_1={90}, YEAR_STATUS_DATE_1={'<$(=Max(YEAR_STATUS_DATE_1))'}>}P_NO_1), YEAR_STATUS_DATE_1={'<$(=Max(YEAR_STATUS_DATE_1))'}>} aggr(FirstSortedValue({<YEAR_STATUS_DATE_1={'<$(=Max(YEAR_STATUS_DATE_1))'}>}UnitsForMetsai, -YEAR_STATUS_DATE_1),P_NO_1))

Re: How to sum the last value for all the members - before the selected year WITH Filter?

sunny_talwar — Wed, 30 Mar 2016 09:02:03 GMT

Do you mean STATUS_1?

=sum({<P_NO_1=P({<STATUS_1={90}>}P_NO_1), YEAR_STATUS_DATE_1={'<$(=Max(YEAR_STATUS_DATE_1))'}>} aggr(FirstSortedValue({<YEAR_STATUS_DATE_1={'<$(=Max(YEAR_STATUS_DATE_1))'}>}UnitsForMetsai, -YEAR_STATUS_DATE_1),P_NO_1))

Re: How to sum the last value for all the members - before the selected year WITH Filter?

Gysbert_Wassenaar — Wed, 30 Mar 2016 09:05:40 GMT

Yeah, that one

Re: How to sum the last value for all the members - before the selected year WITH Filter?

yanivvl0 — Wed, 30 Mar 2016 09:13:18 GMT

hi Sunny - text box pls.

Re: How to sum the last value for all the members - before the selected year WITH Filter?

yanivvl0 — Wed, 30 Mar 2016 09:14:02 GMT

yes you are correct .

Re: How to sum the last value for all the members - before the selected year WITH Filter?

sunny_talwar — Wed, 30 Mar 2016 09:15:52 GMT

So is Gysbert's solution working?

Re: How to sum the last value for all the members - before the selected year WITH Filter?

sunny_talwar — Wed, 30 Mar 2016 09:19:42 GMT

If you are looking for STATUS_1 = 90 and not P_NO_1 where STATUS_1 = 90, then may be this:

=Sum({<STATUS_1 = {90}, YEAR_STATUS_DATE_1={'<$(=Max(YEAR_STATUS_DATE_1))'}>} aggr(FirstSortedValue({<YEAR_STATUS_DATE_1={'<$(=Max(YEAR_STATUS_DATE_1))'}>}UnitsForMetsai, -YEAR_STATUS_DATE_1),P_NO_1))

Re: How to sum the last value for all the members - before the selected year WITH Filter?

yanivvl0 — Wed, 30 Mar 2016 10:46:04 GMT

hi Gysbert , something is wrong ,

i attach the qvw document with a selection that i made , the result supose to be 1021 as you see in the image but we get 1447 :

Re: How to sum the last value for all the members - before the selected year WITH Filter?

Gysbert_Wassenaar — Wed, 30 Mar 2016 10:57:31 GMT

Why should the fifth row with P_NO_1 10253 be excluded?

Re: How to sum the last value for all the members - before the selected year WITH Filter?

yanivvl0 — Wed, 30 Mar 2016 11:01:48 GMT

thanks again Gysbert , i didnwt see it ... MUCH HELP 4 US !!

Re: How to sum the last value for all the members - before the selected year WITH Filter?

yanivvl0 — Wed, 30 Mar 2016 11:03:25 GMT

Yess Sunny - it is . thanks !