Discussion Board for collaboration related to QlikView App Development.
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!
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
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)
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,
FirstSortedValue(Aggr(Sum(BALANCE), DATE_ID), -DATE_ID) // per Tresesco B
This would also work for a YEARMONTH field
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
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.
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).