Compare the past month with the current month in terms of a KPI

[YannikPrill]

Hey QLIK Brains,

so as the title says I want to compare two different time periods. The Periods are described in Calendar Weeks. Now I basically want to show two different KPIs. One should display the value (Lets call it X) for lets say Weeks 1-4 and the other one should display weeks 5-8. The whole thing needs to be flexible so the weeks need to be ongoing without having the need to change the formula.

I hope this is understandable.

Greets,

Yannik

[YannikPrill]

To make it clearer this is the expression that I want to count:

Count({<[Inc Service Name]={'FIS-BMW-PAM-Gespräch'}, [Inc Status Display String]={'Rejected', 'Assigned','In Progess','Waiting for','Change open','Finished'}>}, [Incident-Nr])

Now I need this to be shown for the past 4 Weeks and the 4 Weeks before those 4 Weeks. The Dates are described like this DD.MM.YYYY

[Yoshidaqlik]

Hi

You can try it that way

4 last weeks

Count({<[Inc Service Name]={'FIS-BMW-PAM-Gespräch'}, [Inc Status Display String]={'Rejected', 'Assigned','In Progess','Waiting for','Change open','Finished'},Field_date={">=$(=WeekStart(max(Field_date)),-4)"}  >}, [Incident-Nr])

4 weeks before last

Count({<[Inc Service Name]={'FIS-BMW-PAM-Gespräch'}, [Inc Status Display String]={'Rejected', 'Assigned','In Progess','Waiting for','Change open','Finished'},Field_date={">=$(=WeekStart(max(Field_date)),-8)<$(=WeekStart(max(Field_date)),-4)"}  >}, [Incident-Nr])

Regards

[YannikPrill]

Hey Yoshi,

thanks for the help but QLIK does not seem to recognize the Field_date function.If I apply your formula I dont get any values.

The Data that i upload to the script has time stamps on them. The format is as follows: DD.MM.YYYY

Maybe I need to refer to the days rather then to the Weeks. Does this make any sense?

Greets,

Yannik

[Yoshidaqlik]

Hi

Field_date it's not a function

need to replace with its date dimension

Regards 

[YannikPrill]

Yeah I'm very new to this so please excuse my expressions 😄

So you say instead of using "WeekStart" I should use "DateStart" and change the 4 to 30?

Greets

[Yoshidaqlik]

I think you got it wrong

the number -4 within the formula WeekStart means you will consider the starting day 4 weeks behind the date the formula max will return

if you want to use today you can use Today()

Count({<[Inc Service Name]={'FIS-BMW-PAM-Gespräch'}, [Inc Status Display String]={'Rejected', 'Assigned','In Progess','Waiting for','Change open','Finished'},Field_date={">=$(=WeekStart(TODAY(),-4))"}  >}, [Incident-Nr])

Tidying up the previous formulas looks like I put a ")" in the wrong place

4 last weeks

Count({<[Inc Service Name]={'FIS-BMW-PAM-Gespräch'}, [Inc Status Display String]={'Rejected', 'Assigned','In Progess','Waiting for','Change open','Finished'},Field_date={">=$(=WeekStart(max(Field_date),-4))"}  >}, [Incident-Nr])

4 weeks before last

Count({<[Inc Service Name]={'FIS-BMW-PAM-Gespräch'}, [Inc Status Display String]={'Rejected', 'Assigned','In Progess','Waiting for','Change open','Finished'},Field_date={">=$(=WeekStart(max(Field_date),-8))<$(=WeekStart(max(Field_date),-4))"}  >}, [Incident-Nr])

[Yoshidaqlik]

remembering that you need to change Field_date to the correct name of your date field