topic Calculating Week and Month Analysis in QlikView

Calculating Week and Month Analysis

Wed, 06 Feb 2013 14:14:25 GMT

Hey there

I am quite new to Qlikview, but have built some nice reporting KPIs. However there are some things thare starting to frustrate me, as i'm not sure how to build/create.

Firstly I wish to creat a table showing the quanity for the last 12 months, then the last 6 months, 3 months and then the last month. This would need to be a rolling effect.I have already defined the years; but require a breakdown each period.

Secondly, I need to creat a table to show the amount of cases for each week, with the averafge of last 2 weeks and last 26 weeks (after the last 2 weeks)

Please see the sample data attached (in excel). There are 2 tabs in the file

Look forward to your help.

Re: Calculating Week and Month Analysis

Gysbert_Wassenaar — Wed, 06 Feb 2013 15:04:12 GMT

See attached example. The source data is per date, not already aggregated per week. I used the Qlikview Components library to create a calendar and set analysis variables. Those are used in most of the expressions. It's not possible to combine the period averages in the same pivot table as the week counts. So the result is three tables, no two. I hope this helps you.

Re: Calculating Week and Month Analysis

Thu, 07 Feb 2013 09:45:56 GMT

Thanks for your quick response

In relation to the period analysis (3,6,12 months); i had entered the suggested formulas with the slight alternation to match my data set. I change it up to be a Count formula, as to add count the number of items (and not the sum of).

count( $(vSetRolling12) syscreated)

count( $(vSetRolling6) syscreated)

count( $(vSetRolling3) syscreated)

count( $(vSetMTD) syscreated)

'syscreated' is a date field that is always populated, and is in the data set.

However, All 4 formulas produce the same figures. Where am i going wrong?

Re: Calculating Week and Month Analysis

Fri, 08 Feb 2013 13:17:47 GMT

This is what I have so far

Re: Calculating Week and Month Analysis

Mon, 11 Feb 2013 09:46:24 GMT

This is my current Master Calendar in the script:

Could you please advise the best way to add the additional variables - for 3 month, 6 month and 12 month rolling