7 Replies Latest reply: Jun 7, 2017 7:01 AM by Manish Kachhia

# Calculated Dimension

Hi Friends,

I am stuck in a Calculation

I have Data for Last 4 Years.

But i would want to show only For latest 3 years and latest 12 Quarters and Latest 36 Months only.

I am able to derive this for years with Calculated Dimension. =If(Year>=\$(vYears),Year)

vYears=MAX(Year,2)-1

But i am unable to create the same for Quarters and Months. If you see below image i have Quarters info from 2014 Q1 to 2017 Q3

I would like to show in my graph only 2015 Q1 to 2017 Q3 : Only Latest 12 Quarters Always.

Attached Sample Data file too.

Please can you give me some pointers to it.

• ###### Re: Calculated Dimension

I am assuming that YearQuarter field you have created in Master Calendar.

You can create Dual field for YearQuarter something like below.

Change according to your need. This is just an example.

Dual(Year & '-' & Quarter , AutoNumber(Year & '-' & Quarter,'YearQuarter'))  as YearQuarter

and use as below calculated dimension

=IF(YearQuarter >= (Max(Total YearQuarter)-11) and YearQuarter <= Max(Total YearQuarter), YearQuarter)

and ignore filter of YearQuarter in your expression

SUM({<YearQuarter>}YourExpression)

Also if you want last 12 quarters irrespective of Quarter or year selection then use as below

=IF(YearQuarter >= (Max({1}Total YearQuarter)-11) and YearQuarter <= Max({1}Total YearQuarter),YearQuarter)

• ###### Re: Calculated Dimension

Hi Manish,

Apparently i dont have Master Calendar in my Script. All i have are only those three columns which are attached.

I am Not able to filter has TYQUARTER is coming has string value.

Regards,

Ravi

• ###### Re: Calculated Dimension

Data:

TYQUARTER as TYQUARTERTEMP,

TYMONTH,

TY_NETDOLLARS

FROM

(biff, embedded labels, table is Sheet1\$);

Load Distinct TYQUARTERTEMP, Dual(TYQUARTERTEMP,AutoNumber(Left(TYQUARTERTEMP,4)&Num(Right(TYQUARTERTEMP,1),'00'),'TYQUARTER')) as TYQUARTER Resident Data

Order By TYQUARTERTEMP;

• ###### Re: Calculated Dimension

You can also use

TYQUARTER as dimension with below expression rather than Calculated Dimension

=SUM({<TYQUARTER = {'>=\$(=Max(TYQUARTER)-11)<=\$(=Max(TYQUARTER))'}>}TY_NETDOLLARS)

• ###### Re: Calculated Dimension

Bingo...Its working now..Thanks a Ton  Manish

Also i did the same for Months, but i am getting only 10 Months  instead of 12 Months...

Do i need to make any change in EXP:

Dual(TYMONTH,AutoNumber(Left(TYMONTHTEMP,6)&Num(Right(TYMONTHTEMP,2),'00'),'TYQUARTER')) as TYMONTHTEMP1

=IF(TYMONTHTEMP1 >= (Max(Total TYMONTHTEMP1)-12) and TYMONTHTEMP1 <= Max(Total TYMONTHTEMP1), TYMONTHTEMP1)

Regards,

Ravi

• ###### Re: Calculated Dimension

I am getting now. Once Again Thanks Manish