Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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.


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

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
Contributor III
Contributor III

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.


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