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

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

yanivvl0 — Thu, 25 Feb 2016 14:20:57 GMT

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 !

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

Gysbert_Wassenaar — Thu, 25 Feb 2016 17:02:11 GMT

How about this:

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

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

yanivvl0 — Tue, 01 Mar 2016 09:16:38 GMT

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

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

Gysbert_Wassenaar — Tue, 01 Mar 2016 15:21:53 GMT

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.

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

yanivvl0 — Wed, 02 Mar 2016 10:56:36 GMT

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

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

Gysbert_Wassenaar — Wed, 02 Mar 2016 15:21:56 GMT

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.

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

yanivvl0 — Thu, 03 Mar 2016 09:20:50 GMT

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

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

Gysbert_Wassenaar — Thu, 03 Mar 2016 17:33:37 GMT

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

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

yanivvl0 — Tue, 08 Mar 2016 09:46:21 GMT

hi Gysbert , An excellent solution, seems to work perfectly, many thanks !!

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

gunturhakim — Tue, 23 Feb 2021 10:51:08 GMT

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

that really helping

tanggerang,best regards