Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Hi
Field_date it's not a function
need to replace with its date dimension
Regards
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
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])
remembering that you need to change Field_date to the correct name of your date field