Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mario-sarkis
Contributor II

Week Expression

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

4 Replies

Re: Week Expression

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

filip_duchateau
Contributor II

Re: Week Expression

Hi,

Maybe something like this ?

Regards,

Filip

Not applicable

Re: Week Expression

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
Contributor II

Re: Week Expression

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,