Move Calculated Dimension to Script

[kishoreravi1983]

Hi All,

Please help me to move the below Calculated Dimension to scripting layer as it is consuming more memory.

=IF(TYQUARTER1 >= (Max(Total TYQUARTER1)-11) and TYQUARTER1 <= Max(Total TYQUARTER1), 'FY'& Mid(TYQUARTER1,3,2) & Right(TYQUARTER1,2))

This is how i get the Quarter Data.

I am using this in Bart Chart. I would like to show only last 12 Quarters in my Chart.

  TYQUARTER1

2012-Q1

2012-Q2

2012-Q3

2012-Q4

2013-Q1

2013-Q2

2013-Q3

2013-Q4

2014-Q1

2014-Q2

2014-Q3

2014-Q4

2015-Q1

2015-Q2

2015-Q3

2015-Q4

2016-Q1

2016-Q2

2016-Q3

2016-Q4

2017-Q1

2017-Q2

2017-Q3

2017-Q4

Thanks & Regards,

Ravi

[jonathandienst]

Something like this:

Temp_MaxQ1:

LOAD Max(TYQUARTER1) as MaxQ1       // or MaxString()?

FROM ....;

Let v12Quarters = Peek('MaxQ1') - 11;

DROP Table Temp_MaxQ1;

LOAD

  ...

  TYQUARTER1,

  If(TYQUARTER1 >= v12Quarters), TYQUARTER1, Null()) as DIM_12QUARTERS,

  ...

But unlike the front end expression, this is not dynamic.

[kishoreravi1983]

Hi Jonathan,

Thanks for the Quick Reply. Its working and i am getting last 12 Quarters.

As you mentioned it is not dynamic, would like to clarify if  it would always take 12 Quarters moving forward. If  i move to next year 2018-Q1 its should start counting Quarters from 2018-Q1 to 2015-Q2

Please clarify my doubt.

Thanks,

Ravi

[jonathandienst]

It will take the last 12 quarters in your data, so it should move forward automatically as your data moves forward.

[kishoreravi1983]

Thanks a Lot! it was very helpful.

Regards,

Ravi