Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe something along
=sum({<DATE = {$(date)}>} aggr( if( only({<DATE = {$(date_1)}>}Status) <> only({<DATE = {$(date)}>} Status),1,0), ID))
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
Did you get to solve this ? can you share how?