Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Masi_Sahargahi
New Contributor III

how to handle semi additive measures in qlikview

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!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: how to handle semi additive measures in qlikview

Using Distinct like below?

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

View solution in original post

21 Replies
Highlighted
MVP
MVP

Re: how to handle semi additive measures in qlikview

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.

Highlighted
Masi_Sahargahi
New Contributor III

Re: how to handle semi additive measures in qlikview

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

Highlighted
MVP
MVP

Re: how to handle semi additive measures in qlikview

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)

Highlighted
MVP
MVP

Re: how to handle semi additive measures in qlikview

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Masi_Sahargahi
New Contributor III

Re: how to handle semi additive measures in qlikview

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

Highlighted
MVP
MVP

Re: how to handle semi additive measures in qlikview

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.

Highlighted
Masi_Sahargahi
New Contributor III

Re: how to handle semi additive measures in qlikview

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

Highlighted
MVP
MVP

Re: how to handle semi additive measures in qlikview

M S wrote:

...for every account .

That could be the catch. Try like:

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

Highlighted
Masi_Sahargahi
New Contributor III

Re: how to handle semi additive measures in qlikview

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