Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
kishoreravi1983
Contributor III
Contributor III

Move Calculated Dimension to Script

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

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kishoreravi1983
Contributor III
Contributor III
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kishoreravi1983
Contributor III
Contributor III
Author

Thanks a Lot! it was very helpful.

Regards,

Ravi