Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following requirement ,Please help me on this..
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
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
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
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
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