Get the KPI of the previous week

[Andreas7]

Hi everyone,

I'm new on QlikSense and I would like to  add 2 KPI on my sheet :

- The Events duration of the actual week

- The Events duration of the previous week

I have not a developper profil, so i don't have access to modify measure : I would like to do this only in a case "KPI Indicator" with a formule.

I try this but it doesn't work (display the event duration of the current week only...) :  Sum({<DateField={'$(=Week(WeekStart(Today()) - 7))'}>} EventsNetDuration)

Thanks a lot,

Andreas

[Oleg_Troyansky · Accepted solution]

Hi Andreas,

You are using Set Analysis, which is similar to selecting the value, calculated within the {...}, in the specified field. So, your current formula is trying to select the Week number in the Date field. These are two different values, and they are not comparable.

Ideally, I'd recommend adding Calendar flags for "this week" and "previous week", however if you don't have access to the data load script, then your options are limited. I'd recommend an Advanced Search filter that selects those dates that share the same WeekStart with the current date - 7. It would look like this:

Sum({<DateField={"=WeekStart(DateField)=WeekStart(Today()-7)"}>} EventsNetDuration)

It would take too long to explain all of the syntax here - just make sure to enclose the search in double quotes and start it with the equals sign =.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Cheers,

[Andreas7]

Thanks a lot for your response Oleg !

Based on your message I find a solution if it can help someone in the same case :

KPI of this week : =Sum({<Week = {"$(=Week(Today()))"}>} EventDuration)

KPI of the previous week : =Sum({< Week = {"$(=Week(Today()-7))"}>} EventDuration)

With that I can just divide these to have the variance of my KPI 🙂

Have a great day,

Andreas

[Oleg_Troyansky]

Hi Andreas,

Kudos for finding a solution that works for you! One thing to keep in mind - if your data could include more than one year, then Week number alone is not a sufficient condition - the same week numbers may exist in multiple years. Otherwise, this is a viable solution.

Cheers,