Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey All
here is my question :
KEY Week Status
ID1 2017-13 Open
ID1 2017-14 Closed
ID2 2017-13 Open
ID2 2017-14 On hold
ID3 2017-13 Open
ID3 2017-14 Open
My question is how I can write in a expression the number of IDs that changed its status from last week to current week (max(week))
in the example above the result will be ID1 and ID2 changes their status so the answer will be " 2 "
Noting that my week form is as the example Year-Weeknumber
hope you can help,
thank you
May be this
Count(DISTINCT {<KEY = {"=Only({<WeekNum = {'$(=Max(WeekNum))'}>} Status) <> Only({<WeekNum = {'$(=Max(WeekNum)-1)'}>} Status)"}>} KEY)
Where you create WeekNum in the script like this
Year(Date)*100 + Week(Date) as WeekNum
Hi,
Maybe something like this ?
Regards,
Filip
Hello,
Try to join this table to the original one ( suppose that your table name is A):
left join(A)
LOAD
KEY,
Week,
if([Status= Previous([Status]), 0, 1 ) as countChange
Resident
A
and then in the dashboard just insert this expression:
sum(countChange)
Let me know if it worked,
Rima
Hi Filip thank you for you reply I think this will work in case I have 2 dates only, but unfortunately I have more that two week for each ID in My Data Set.
Thank you,