AGGR in Calculated Dimension

[#1]

Dear QV Expert,

The financial Year, From Dec to November.

So Dec to Feb - 1QYY,  Mar to May - 2QYY, Jun to Aug - 3QYY, Sep - Nov - 4QYY.

The below dimension can be handled in the script but we need to calculate based on the arbitrary date in the Calendar Object.

If retrieved for 3 months, For Ex, Dec15 to Feb16,

Then the Primary Dimension displays in the chart as 1Q16. & the expression displays the correct value.

If i retrieve more than 3 months like 6 or 9 or 12 months, The dimension should split as 1Q16, 2Q16 and its respective expression values.

But i am getting the only one value not split in to two or three or four dimensions.

The reason is AGGR is used for Client Id, WD_CLIENT.CLIENT_DS .

Can anyone suggest me how i can include the Quarters in the Group by .

A Calculated dimension,

-- If Warranty Start Date is lesser than the Retrieval start date then update it to Retrieval Start Date,

AGGR(IF([WD_CERTIFICATE.EXT_WRNTY_STR_DT]<$(vFiscalStartDate),

               if(date($(vFiscalStartDate),'MMM')= 'Dec' or date($(vFiscalStartDate),'MMM')= 'Jan' or

               date($(vFiscalStartDate),'MMM')= 'Feb' ,'1Q' & date($(vFiscalEndDate),'YY'),                   ---1QYY

               if(date($(vFiscalStartDate),'MMM')= 'Mar' or date($(vFiscalStartDate),'MMM')= 'Apr' or

               date($(vFiscalStartDate),'MMM')= 'May' ,'2Q' & date($(vFiscalEndDate),'YY'),                  -- 2QYY

               if(date($(vFiscalStartDate),'MMM')= 'Jun' or date($(vFiscalStartDate),'MMM')= 'Jul' or

               date($(vFiscalStartDate),'MMM')= 'Aug' ,'3Q' & date($(vFiscalEndDate),'YY'),                   --3QYY

               '4Q' & date($(vFiscalStartDate),'YY')))),                                                                           --4QYY

-- If Warranty Start Date is greater than the Retrieval end date then update it to Retrieval Start Date,

IF([WD_CERTIFICATE.EXT_WRNTY_STR_DT]>$(vFiscalEndDate),

          if(date($(vFiscalStartDate),'MMM')= 'Dec' or date($(vFiscalStartDate),'MMM')= 'Jan' or

          date($(vFiscalStartDate),'MMM')= 'Feb' ,'1Q' & date($(vFiscalEndDate),'YY'),

          if(date($(vFiscalStartDate),'MMM')= 'Mar' or date($(vFiscalStartDate),'MMM')= 'Apr' or

          date($(vFiscalStartDate),'MMM')= 'May' ,'2Q' & date($(vFiscalEndDate),'YY'),

          if(date($(vFiscalStartDate),'MMM')= 'Jun' or date($(vFiscalStartDate),'MMM')= 'Jul' or

          date($(vFiscalStartDate),'MMM')= 'Aug' ,'3Q' & date($(vFiscalEndDate),'YY'),

          '4Q' & date($(vFiscalStartDate),'YY')))),

     if(date($(vFiscalStartDate),'MMM')= 'Dec' or

     date($(vFiscalStartDate),'MMM')= 'Jan' or

     date($(vFiscalStartDate),'MMM')= 'Feb' ,'1Q' & date($(vFiscalEndDate),'YY'),

     if(date($(vFiscalStartDate),'MMM')= 'Mar' or

     date($(vFiscalStartDate),'MMM')= 'Apr' or

     date($(vFiscalStartDate),'MMM')= 'May' ,'2Q' & date($(vFiscalEndDate),'YY'),

     if(date($(vFiscalStartDate),'MMM')= 'Jun' or date($(vFiscalStartDate),'MMM')= 'Jul' or

     date($(vFiscalStartDate),'MMM')= 'Aug' ,'3Q' & date($(vFiscalEndDate),'YY'),

     '4Q' & date($(vFiscalStartDate),'YY')))))),WD_CLIENT.CLIENT_DS )

The ScreenShot Below,

The right chart is correct & the left chart requires changes in calculated dimension.

Thanks,

Sasi

[marcus_sommer]

I think you should use a fiscal calendar with quarter as a native field. Here you will find many useful informations and examples: How to use - Master-Calendar and Date-Values.

- Marcus

[#3]

Thanks Marcus.

Can you please help me in slicing the quarters in the dimensions.

Can anyone please help me out.

Thanks,

Sasi