Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

reivax31
Contributor III

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

Hi Qlikers,

In the table bellow see exemple of data set:

   

Extract_DateID[Diff.number_of_changes]Sales
06/01/2018A010
06/01/2018B820
06/01/2018C530
15/01/2018A011
15/01/2018B322
15/01/2018C133
25/01/2018A015
25/01/2018B025
25/01/2018C735

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

1 Solution

Accepted Solutions
thiago_justen
Valued Contributor III

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

Xavier,

Is it what you want to?

Capturar.PNG

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 Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
3 Replies
thiago_justen
Valued Contributor III

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

Xavier,

Is it what you want to?

Capturar.PNG

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 Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
thiago_justen
Valued Contributor III

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

Xavier, any news about your question?

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
reivax31
Contributor III

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

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.

Community Browser