Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
yanivvl0
Contributor III

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

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 !

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
MVP & Luminary
MVP & Luminary

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

How about this:

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


talk is cheap, supply exceeds demand
yanivvl0
Contributor III

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

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
sum_prev year -1.PNG

hope for solutuin ...

MVP & Luminary
MVP & Luminary

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

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.


talk is cheap, supply exceeds demand
yanivvl0
Contributor III

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

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

MVP & Luminary
MVP & Luminary

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

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.


talk is cheap, supply exceeds demand
yanivvl0
Contributor III

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

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 :

לכידה.PNG

THANKS FOR THE Instructions !!

MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand

View solution in original post

yanivvl0
Contributor III

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

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