Challenge: Convert Monthly Totals to Daily Average

[wdchristensen]

Hello Everyone,

I am attempting to create an application to show historical budgeted volume against actual volume on a daily basis for up to 7 years in the past. For this scenario, the budgeted volume is calculated per month and must be converted to a daily volume to generate a baseline for comparison. The source data is from Lawson and it not is in a user friendly format. I have reformatted the budgeted volume data and generated daily averages in 15 lines of TSQL. I would like to see a Qlik solution that is comparable. I was originally thinking of a master calendar but that requires thousands of lines and must be maintained so I immediately loose the challenge. I trying to grow my QLIK skills but I know I can’t create a comparable solution in Qlik, can you? If you think it can’t be done, I would like to hear that too! Please see the attached SQL file for example data and the recursive SQL solution. I appreciate all those who took the time to read this this discussion. Thanks!

[robert_ramos]

It is not possible in QLIK SENSE 3.2.

[dwforest]

Do something similar, payperiod to day; which is easier because its a fixed interval.

So, my adjusted untested code would be:

LOAD

Date#(BudgetStartDate) + Iterno() AS "Date",

    "Cost Center",

    "Labor Distribution",

    "Prod/Non Prod",

    JobClass,

    ROUND("BudgetHours"/Day(MonthEnd(BudgetStartDate)),.01) AS "BudgetHours"

WHILE IterNo() <= Day(MonthEnd(BudgetStartDate));

[wdchristensen]

Hi David,

I have not written a load script with that kind of logic before but conceptually I think it is a great idea. I will try to implement your solution and let  you know how it goes. As with all your replies, this information is very helpful. Thanks for taking the time to help me figure this out!

[jpitfield]

I was originally thinking of a master calendar but that requires thousands of lines and must be maintained so I immediately loose the challenge.

Calendars in Qlikview are relatively simple to create, I have attached a simple solution for you to review.

For ease of maintenance for my Calendar Scripts I write them to a QVS file as subs then in my applications 'Call' them in my load scripts as reusable code.

eg.

$(Include=PathtoQVS\Calendar.qvs);

Call Calendar(Year(Today())-1,Year(Today()));
(my calendar scripts accept a year and generate the start and end dates from them)

This way if I modify the qvs and the changes are applied to all of my apps on the next reload. There are many examples of Calendar code if you search that you can use

HTH

Jim