Calculated Dimension

[kishoreravi1983]

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.

[MK_QSL · Accepted solution]

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)

[kishoreravi1983]

Hi Manish,

Thanks for your Reply.

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

[MK_QSL]

Data:

LOAD TYYEAR,

     TYQUARTER as TYQUARTERTEMP,

     TYMONTH,

     TY_NETDOLLARS

FROM

[..\Downloads\Sample Data File.xls]

(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;

Use above script and follow all other steps from previous reply.

[MK_QSL]

You can also use

TYQUARTER as dimension with below expression rather than Calculated Dimension

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

[kishoreravi1983]

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

[kishoreravi1983]

I am getting now. Once Again Thanks Manish

[MK_QSL]

Please close the thread by selecting appropriate answer.