Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
regowins
Creator II
Creator II

Convert monthly data by location and year to daily

Hi,

I have a budget table by month by location going back 3 years...( below & attached sample data) .

           

DPT_IDMONTH_DATELOCATIONBUDGETEDDATE_YEARDATE_MONTH
11/1/20122155520121
22/1/20122185620122
33/1/20122167820123
44/1/20122142520124
55/1/20122178620125
66/1/20122178620126
77/1/20122142220127
88/1/20122178620128
99/1/20122143520129
1010/1/201221453201210
1111/1/201221963201211
1212/1/201221424201212

I need to recreate the exact same table  as above  but convert the monthly budget to daily for each month by location. so for example, I would take Jan 555 / 31 = 17.90 and then generate 31 dates with 17.9 for daily budget ....Feb 856/29 = 29.5 for 29 days... till the end of the year.   Then loop for the next location / Year and monthly budget to convert to daily.

Any idea how I would go about doing this in the script?

Thanks for the help!

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this

Data:

LOAD

  DPT_ID,

     MONTH_DATE,

     LOCATION,

     BUDGETED,     

     DATE_YEAR,

     DATE_MONTH,

  BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,

  Date(MONTH_DATE + IterNo() - 1) AS DATE

WHILE Num(MonthStart + IterNo() - 1) <= MonthEnd;

LOAD DPT_ID,

     Date(MONTH_DATE) AS MONTH_DATE,

     LOCATION,

     BUDGETED,     

     DATE_YEAR,

     DATE_MONTH,

     Num(MonthEnd(Date(MONTH_DATE))) AS MonthEnd,

     Num(MonthStart(Date(MONTH_DATE))) AS MonthStart    

FROM

(ooxml, embedded labels, table is [ Sample]);

View solution in original post

4 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this

Data:

LOAD

  DPT_ID,

     MONTH_DATE,

     LOCATION,

     BUDGETED,     

     DATE_YEAR,

     DATE_MONTH,

  BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,

  Date(MONTH_DATE + IterNo() - 1) AS DATE

WHILE Num(MonthStart + IterNo() - 1) <= MonthEnd;

LOAD DPT_ID,

     Date(MONTH_DATE) AS MONTH_DATE,

     LOCATION,

     BUDGETED,     

     DATE_YEAR,

     DATE_MONTH,

     Num(MonthEnd(Date(MONTH_DATE))) AS MonthEnd,

     Num(MonthStart(Date(MONTH_DATE))) AS MonthStart    

FROM

(ooxml, embedded labels, table is [ Sample]);

regowins
Creator II
Creator II
Author

Your solution was perfect!  Thank You!!!!

tresesco
MVP
MVP

Nice solution Jagan!

however, a small but significant bug is there in this solution.

BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,

MonthEnd would have last millisecond of the last date of the month which would result a difference (MonthEnd-MonthStart) by extra 1 (0.99..). That is for ex. - the January value will be divided by 32 (31.99...) instead of 31. You can handle this by two alternate ways:


BUDGETED/ (Floor(MonthEnd) - MonthStart + 1) AS Budget_Daily

Or,

BUDGETED/ Day(MonthEnd) AS Budget_Daily

regowins
Creator II
Creator II
Author

Thanks for the catch Tresesco!