AGGR in Calculated Dimension

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: AGGR in Calculated Dimension

marcus_sommer — Fri, 22 Apr 2016 11:58:43 GMT

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

Re: AGGR in Calculated Dimension

Fri, 22 Apr 2016 15:44:56 GMT

Thanks Marcus.

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

Can anyone please help me out.

Thanks,

Sasi

topic Re: AGGR in Calculated Dimension in QlikView

AGGR in Calculated Dimension

Re: AGGR in Calculated Dimension

Re: AGGR in Calculated Dimension