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

[yanivvl0]

hi to all ,

i need to sum the values UnitsForMet for each P_NO_1 (Project) from the last year occurrence (YEAR_STATUS_DATE_1)

before the selected year ( vSelectedYear = getfieldselections ( YEAR_STATUS_DATE_1 ) ) ,

i tried this command its good without select the YEAR_STATUS_DATE_1  , but  by selecting 1 year

its oblivious the set-analasys filter :

=sum ( aggr(firstSortedValue({$<[YEAR_STATUS_DATE_1]={'<$(=vSelectedYear)'}>}UnitsForMet  ,  -YEAR_STATUS_DATE_1), P_NO_1  )  )

is ther any way to solve it ? 

thanks !

[Gysbert_Wassenaar]

How about this:

sum({<YEAR_STATUS_DATE_1={$(=Max(YEAR_STATUS_DATE)-1)}>}UnitsForMet)

[yanivvl0]

hi Gysbert , thanks for helping us - your answers always Appreciate !!

as you can see when - using the Max(YEAR_STATUS_DATE)   : (2016)  Causes miss the cases when the last previos year is not exist for P_NO_1 , in this example the value 1180 - in 2014   -   did not summarized

hope for solutuin ...

[Gysbert_Wassenaar]

Maybe this:

sum(aggr(FirstSortedValue({<YEAR_STATUS_DATE_1={'<$(=Max(YEAR_STATUS_DATE))'}>}UnitsForMet, -YEAR_STATUS_DATE_1),P_NO_1))

If that doesn't work, please post a small qlikview document that demonstrates the problem.

[yanivvl0]

hi again , sorry , but i cant see the option to add the QV doc here ( only an image ) how can i do this ? thanks.

[Gysbert_Wassenaar]

Open the discussion itself, because when replying from your inbox you can't change to the Advanced Editor. Click Reply to open the reply editor and then click the use advanced editor link at the top right. Then you can use the Attach link in the bottom right to add an attachement.

[yanivvl0]

hi Gysbert , for more Clarity , in this example , if i select the year 2015 then values Marked in yellow must be Summarized

because them year is before 2015 and its the last occurrence for each P_NO_1 :

THANKS FOR THE Instructions !!

[Gysbert_Wassenaar · Accepted solution]

Try this expression:

=sum({<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 , An excellent solution, seems to work perfectly, many thanks !!

[gunturhakim]

Hi, that very simmilar what i need, thanks to you guys for your explantion

that really helping

tanggerang,best regards