topic Re: Quarter / Month Bands in QlikView

Quarter / Month Bands

milindnikumbh — Wed, 29 Nov 2017 07:57:00 GMT

Dear Experts ,

How to create Month / Quarter bands from date. Following is my test data and wants graph as attached.

Date	Qty
1-Jan-16	45
1-Feb-16	80
1-Mar-16	40
1-Jan-17	55
1-Feb-17	15
1-Mar-17	45

Your help is highly appreciated.

thanks.

Regards,

Milind.

Re: Quarter / Month Bands

sunny_talwar — Wed, 29 Nov 2017 07:58:49 GMT

Create a month and year field in the script like this

LOAD Date,

Year(Date) as Year,

Month(Date) as Month,

Qty

FROM ...;

and then create a bar chart like this

Dimension

Month

Year

Expression

Sum(Qty)

Re: Quarter / Month Bands

milindnikumbh — Wed, 29 Nov 2017 08:02:06 GMT

Thanks Sunny for your prompt reply. Loading data in this way is the only way for this ? I had date in fact table and also calander data is also there.

Re: Quarter / Month Bands

shraddha_g — Wed, 29 Nov 2017 08:02:59 GMT

Create a Month and Quarter Fields in Load Script as below:

Month(Date(Date#(Date,'D-MMM-YY'))) as Month,

Dual('Q'&Ceil(Month(Date(Date#(Date,'D-MMM-YY')))/3),Ceil(Month(Date(Date#(Date,'D-MMM-YY')))/3)) as Quarter

and use these fields in Chart as Dimension

Re: Quarter / Month Bands

shraddha_g — Wed, 29 Nov 2017 08:04:29 GMT

then directly you can use calculated Dimension instead of doing this in load sript

Re: Quarter / Month Bands

milindnikumbh — Wed, 29 Nov 2017 08:08:43 GMT

Hello Shraddha,

Thanks. But I want Two Jan side by side. It will be great if you check the attachment.

I am not able to club two Jan ( Jan-16/Jan-17 side by side)

Hope you get my point.

Re: Quarter / Month Bands

shraddha_g — Wed, 29 Nov 2017 08:13:23 GMT

You can use Dimensions as

1st - Month(Date(Date#(Date,'D-MMM-YY'))) for Month

2nd -. Year(Date(Date#(Date,'D-MMM-YY'))) for Years

Measure - Sum(Qty)

this way you will get 2 bars at JAN showing one for 2016 and another for 2017

Re: Quarter / Month Bands

milindnikumbh — Wed, 29 Nov 2017 08:45:40 GMT

Thanks. Resolved.