Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Slowly Changing Dimensions Sum

Hi all,

I am struggling with my syntax and set analysis.

I need to get the sum of the status OPN. When changing the date it should only include the latest Status for that ID.

Using the below table: If we selected the date 2017/02/04 the sum of amount would be 150+200 = 350. We wouldn't select 2017/01/31 as that status has changed.

DateStatusAmountID
2017/01/31OPN100AAA
2017/02/01CLS100AAA
2017/02/04FNL100AAA
2017/01/30OPN150BBB
2017/02/03OPN200CCC

I am not sure which variables to create or the correct syntax/flags to use to create the text box to show this value.

Thank you for any help!

3 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

You could do this in a text box:

Sum(Aggr(FirstSortedValue(Amount, -Date), ID))

Although I would create a derived flag value in the load script and then use  a simple set expression. That will generally be quicker than the Aggr() expression above.

Sum({<Latest = {1}>} Amount)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Thank you for your reply.

I have done the flag, however, the issue comes up when changing the date to look back at previous dates.

Having that flag will include incorrect statuses that should not be there. I am not sure how to create the correct flag.

Eg: Selecting 2017/01/31 should give a value of 250 as at that date there were 2 ID's in OPN status.

jonathandienst
Partner - Champion III
Partner - Champion III

If you want to dynamically respond to data selections, you cannot rely on the derived flag field. You will need to use an expression like  my first proposal:

Sum(Aggr(FirstSortedValue(Amount, -Date), ID))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein