count status which have changed since yesterday

[#1]

Hi,

I had a similar question last week, and got a response using numbers.

But now I have I need to do the same kind of thing with a string

This is my structure table

ID     DATE                STATUS

1      $(date_1)          A        

2      $(date_1)          A   

3      $(date_1)          A  

1      $(date)               A   

2      $(date)               B   

3      $(date)               A   

I want to count the distinct ID, which have DATE = $(v_date), and a STATUS different of the STATUS for the all the previous DATE for the same ID.

(in my example with $(v_date) the answer is 1 : the STATUS for ID 1 and 3 did not change, and the STATUS for ID 2 is different than the one of the day before $(date_1))

Thank you for your help !

Yannick

[swuehl]

Maybe something along

=sum({<DATE = {$(date)}>} aggr( if( only({<DATE = {$(date_1)}>}Status) <> only({<DATE = {$(date)}>} Status),1,0), ID))

[datanibbler]

Hi,

I would consider a two-step process (three once you have it started) to have (several) "relatively" easy and manageable formulas:

- (Check whether the status of a specific ID for $(v_date) is already in your list for that ID or not)

- If it is not there, write the current status of every ID into the list, together with the date (growing just about every day)

- Count the IDs for which, on a given date ($(v_date), you have a status that's not in the list.

We have already built up some historization_apps in Qlikview that essentially do just that

HTH

Best regards,

DataNibbler

[Anonymous]

Did you get to solve this ? can you share how?