topic Re: Week to Month Rollups in QlikView

Week to Month Rollups

mayankraoka — Fri, 01 Jul 2016 10:31:07 GMT

Hi All,

I have the following requirement ,Please help me on this..

data is available at week level. customer needs weekly and monthly view. we usually follows 4-4-5 rolls up from week to month. Refer below example.

WKLY_CYC_DT MTH_ID

2015-12-04 00:00:00 201512

2015-12-25 00:00:00 201512

2015-12-11 00:00:00 201512

2016-01-01 00:00:00 201512

2015-12-18 00:00:00 201512

2016-01-15 00:00:00 201601

2016-01-29 00:00:00 201601

2016-01-08 00:00:00 201601

2016-01-22 00:00:00 201601

2016-02-05 00:00:00 201602

2016-02-26 00:00:00 201602

2016-02-19 00:00:00 201602

2016-02-12 00:00:00 201602

With this approach Dec 15 will show inflated data as its for 5 weeks as compared to Jan 16 and Feb 16. We want to avoid this and hence need alternative way to roll-up data at month?

Regards,

Mayank

Re: Week to Month Rollups

marcus_sommer — Fri, 01 Jul 2016 11:31:41 GMT

I think a master-calendar to your week/month logic could be useful. This one: 4-5-4 Master Calendar is 4-5-4 and not 4-4-5 but this could be adjusted.

- Marcus

Re: Week to Month Rollups

mayankraoka — Fri, 01 Jul 2016 20:53:25 GMT

But in this way the comparison is not valid.One month has more weeks and 1 less.Any other better way to do rollup in qlikview?

Regards,

Mayank

Re: Week to Month Rollups

marcus_sommer — Sat, 02 Jul 2016 06:58:02 GMT

Months and weeks are different things and even you take only one of them you will have some inaccuracies with holidays, switching from weekdays over the years and some more. But if you tries to compare your data against both dimensions you will increase these inaccuracies.

One way to make such comparings more useful could be not to look on the total amounts else on the rate per workingday. Another thought is to create a master-calendar with 13 months, each with 4 weeks which meant you need only a way to handle the 365/366 day in year / shift year - everything else would be equal. This could be a starting-point for this:

calendar:

load *, ceil(daynumberofyear(Date) / 7) as Week2, ceil(ceil(daynumberofyear(Date) / 7) / 4) as Month2

resident calendar_temp;

- Marcus

Re: Week to Month Rollups

Anonymous — Sat, 02 Jul 2016 07:27:30 GMT

This is another example

CREATE VOLATILE TABLE STORE_QTY

(

STORE_ID INTEGER,

SOLD_DATE DATE,

PRODUCT_ID INTEGER,

QUANTITY INTEGER

)

ON COMMIT PRESERVE ROWS;

If you want to calculate the number of products of same kind sold from the table, along with the total number of products sold, then you can use a ROLLUP function as :

SELECT PRODUCT_ID

,SUM(QUANTITY) AS TOTAL_QUANTITY

FROM STORE_QTY

GROUP BY ROLLUP (PRODUCT_ID)

ORDER BY 1;

I hope this resolve your problem even for further resolving you can go for checking out thus link

https://goo.gl/XhM5DI