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

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

reivax31 — Thu, 25 Jan 2018 16:09:36 GMT

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

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

Thiago_Justen_ — Sat, 27 Jan 2018 12:36:51 GMT

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)

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

Thiago_Justen_ — Tue, 06 Feb 2018 22:47:27 GMT

Xavier, any news about your question?

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

reivax31 — Wed, 07 Feb 2018 07:42:48 GMT

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.