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!

[tresesco]

Could you explain a bit more in details about what you are really struggling with? may be sample data set would help us understand better.

[Masi_Sahargahi]

My data set is something like this :

DATE_ID,  ACCOUNT_ID,   BALANCE

20150101,        1,                1000000

20150101,        2,                 500000

20150201,        3,                  60000

20151220,        4,                  70000

when user filters year :2015, I want to see BALANCE:70000

when user filters year :2015-quarter1, I want to see BALANCE:60000

when user filters year :2015-jan, I want to see BALANCE:1500000(1000000+500000)

i hope i could explain clearly

[tresesco]

So you want the latest balance at day level (aggregated). If so, you could expression like below in a text box:

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

[jonathandienst]

Do I understand you correctly - you would like to see a sum if the user selects a month, and the last value if anything else is selected?

Assuming you have a MONTH field then something like

If(GetSelectedCount('MONTH') = 1,

  Sum(BALANCE),

  FirstSortedValue(Aggr(Sum(BALANCE), DATE_ID), -DATE_ID)  // per Tresesco B

)

This would also work for a YEARMONTH field

[Masi_Sahargahi]

thanks for your reply,but it doesn't work when you have more than one record per day

here 2015-Jan-1 has two records and the text box doesn't show anything

[tresesco]

It should work because aggregation sum() is used to handle that multiple data per day. We may be missing at some point. Could you share your sample qvw that shows the issue? May be I would be able to help you better then.

[Masi_Sahargahi]

Actually I would like to see sum of last balances for every account over any level of time dimension(year or quarter or month or day).

[tresesco]

M S wrote:

...for every account .

That could be the catch. Try like:

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

[Masi_Sahargahi]

I use personal edition and I hope you can open attached file