how to handle semi additive measures in qlikview

[Masi_Sahargahi]

Hello all!

I have a problem with handling semi additive measures in Qlikview which i couldn't find a solution for.

can any body help me out of this:

i have a table which stores customer's last account balance.i want to show the last balance for every level of date hierarchy that user filters(year,quarter,month,day).

tip:just when a transaction occurs and affects on customer balance,a record(last balance) will be inserted in table,meaning we don't have all the last balances for every day in table.

any help would be highly  appreciated!

[Masi_Sahargahi]

still doesn't work

[antoniotiman]

May be You need to add DISTINCT in FirstSortedValue() because same values in Date Field.

[tresesco · Accepted solution]

Using Distinct like below?

=FirstSortedValue( DISTINCT Aggr(Sum(BALANCE), DATE_ID), -DATE_ID)

[Masi_Sahargahi]

it worked!

final expression:

FirstSortedValue(DISTINCT Aggr(Sum(BALANCE), DATE_ID), -DATE_ID)

Thank you so much!

[Masi_Sahargahi]

Yes,its what exactly I was looking for .

Thank you so much for helping!

[tresesco]

Please close the thread by marking helpful/correct answers for better reference by other people searching for similar solution.

[Masi_Sahargahi]

the main problem was solved.

but there is still another issue:

i don't have all the account's balance in each month,

meaning account_id=1 may have last balance in Jan-2015 (not in Feb- 2015),but when I filter Feb-2015 I want to see its last balance too,is there any solution for that ?

[tresesco]

May be this?

=FirstSortedValue( {<DATE_ID={"=Len(trim(BALANCE))>0"}>} DISTINCT Aggr(Sum( {<DATE_ID={"=Len(trim(BALANCE))>0"}>} BALANCE), DATE_ID), -DATE_ID)

[Masi_Sahargahi]

Actually it didn't work.

what I need is something like this :

=FirstSortedValue(

{<DATE_ID<={"MAX OF DATE_ID WITHIN FILTERED LEVEL(YEAR,QUARTER,MONTH)"}>} 

DISTINCT Aggr(Sum(BALANCE), DATE_ID), -DATE_ID)

[tresesco]

Please share your sample qvw with representative data.