How to identify ID that has informations changed in time and find its most recent sales value

[reivax31]

Hi Qlikers,

In the table bellow see exemple of data set:

Extract_Date	ID	[Diff.number_of_changes]	Sales
06/01/2018	A	0	10
06/01/2018	B	8	20
06/01/2018	C	5	30
15/01/2018	A	0	11
15/01/2018	B	3	22
15/01/2018	C	1	33
25/01/2018	A	0	15
25/01/2018	B	0	25
25/01/2018	C	7	35

Every time I have a new set of data (Extract_Date) I calculate the number of fields that have been changed and put that value in field [Diff.number_of_changes]. Now I want to set KPI that will allow to see how many ID have changed between min date (06/01/2018) and max date (25/01/2018). for this I use expression : count(distinct(if([Diff.number_of_changes]>0,ID))) . And this works fine

It gets more complicated for me when I want to find the value of Sales at my max date (25/01/2018) only if a change has been made in the past. So for A value returned should be 0, for B 25 and for C 35.

I hope my explanation is understandable.

Thanks for your help

[Thiago_Justen_ · Accepted solution]

Xavier,

Is it what you want to?

Expression:

If(Sum(If([Diff.number_of_changes]>0,1,0))>0, sum(aggr(sum({<Extract_Date={">=$(=Max(Extract_Date))"}>}Sales),ID)),0)

[Thiago_Justen_]

Xavier, any news about your question?

[reivax31]

Hi sorry I didn't see your answer. I tried your solution and it works but I had already change my data base in order to have the expected result without having a "complicate formula" in my visualisation. What I did is to keep in a field the date of the last time my item has been changed. So if date is superior to my min(date) this means my Item has been changed between my min and max date.