Slowly Changing Dimensions Sum

[Anonymous]

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.

Date	Status	Amount	ID
2017/01/31	OPN	100	AAA
2017/02/01	CLS	100	AAA
2017/02/04	FNL	100	AAA
2017/01/30	OPN	150	BBB
2017/02/03	OPN	200	CCC

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!

[jonathandienst]

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)

[Anonymous]

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]

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