# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

Tags (3)
1 Solution

Accepted Solutions
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
8 Replies
MVP & Luminary

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

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

talk is cheap, supply exceeds demand
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

hope for solutuin ...

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

## 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
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 :

THANKS FOR THE Instructions !!

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