Period Grouping

[upaliwije]

Hi

I have developed a QV document where I have date for 4 year stating from 2012 and monthly data as well. For me to created charts I want group months (create period dimension) in the following may

1  for 12 Months

2  for the months of May June And July

3  for Balance 9 months

Pls tell what the easiest way to do this

Thanks

[HirisH_V7]

Hi,

May be like this,

Extract Months and MonthNum from your date fields like this,

Data:
LOAD Customer,
    [Sales Order ID],
    ShipDate as Date,
    Month(ShipDate) as Month,
    NUm( Month(ShipDate)) as MonthNum,
    Product,
    Sales,
    Quantity
FROM
[Sales Orders.xls]
(biff, embedded labels, table is [Sales Orders$]);

Then at front end,

For 12 Months In Your Expression,

Sum({<Date={">=$(=MonthStart(Max(Date), -11))<=$(=Date(Max(Date)))"}>} Sales )

For Your Required Three Months as Dimension,

=If(Match(Month,'May','Jun','Jul'),Month,Null())

For Excluding 3 Months,

=If(Not Match(Month,'May','Jun','Jul'),Month,Null())

Hope this helps,

PFA,

Hirish

[upaliwije]

Thanks

C I do it in load script please ?

[HirisH_V7 · Accepted solution]

Hi,

use this both,

If(Match(Month(ShipDate),'May','Jun','Jul'),Month(ShipDate),Null()) as ThreeMonth,

 If(Not Match(Month(ShipDate),'May','Jun','Jul'),Month(ShipDate),Null()) as NotThreeMonth,