Week Expression

[mario-sarkis]

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

[sunny_talwar]

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

[Anonymous]

Hi,

Maybe something like this ?

Regards,

Filip

[#4]

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

[mario-sarkis]

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,