Quarter / Month Bands

[milindnikumbh]

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.

[sunny_talwar]

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)

[milindnikumbh]

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.

[shraddha_g]

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

[shraddha_g]

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

[milindnikumbh]

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.

[shraddha_g]

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

[milindnikumbh]

Thanks. Resolved.