Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
yanivvl0
Creator III
Creator 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
Gysbert_Wassenaar

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

9 Replies
Gysbert_Wassenaar

How about this:

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


talk is cheap, supply exceeds demand
yanivvl0
Creator III
Creator III

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

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.


talk is cheap, supply exceeds demand
yanivvl0
Creator III
Creator III

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.


talk is cheap, supply exceeds demand
yanivvl0
Creator III
Creator III

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

Gysbert_Wassenaar

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
Creator III
Creator III

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

gunturhakim
Contributor
Contributor

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

that really helping

tanggerang,best regards