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

[yanivvl0]

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.

[sunny_talwar]

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

[Gysbert_Wassenaar]

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

[sunny_talwar]

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

[Gysbert_Wassenaar]

Yeah, that one

[yanivvl0]

hi Sunny - text box pls.

[yanivvl0]

yes you are correct .

[sunny_talwar]

So is Gysbert's solution working?

[sunny_talwar]

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

[yanivvl0]

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 :