topic Re: Week Expression in QlikView

Week Expression

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Week Expression

sunny_talwar — Fri, 16 Jun 2017 12:51:19 GMT

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

Re: Week Expression

Anonymous — Fri, 16 Jun 2017 12:57:24 GMT

Hi,

Maybe something like this ?

Regards,

Filip

Re: Week Expression

Fri, 16 Jun 2017 13:06:41 GMT

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

and then in the dashboard just insert this expression:

sum(countChange)

Let me know if it worked,

Rima

Re: Week Expression

mario-sarkis — Fri, 16 Jun 2017 13:14:50 GMT

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,