topic Re: Move Calculated Dimension to Script in QlikView

Move Calculated Dimension to Script

kishoreravi1983 — Mon, 23 Oct 2017 06:37:54 GMT

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

Re: Move Calculated Dimension to Script

jonathandienst — Mon, 23 Oct 2017 07:11:43 GMT

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.

Re: Move Calculated Dimension to Script

kishoreravi1983 — Mon, 23 Oct 2017 07:49:04 GMT

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

Re: Move Calculated Dimension to Script

jonathandienst — Mon, 23 Oct 2017 08:21:02 GMT

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

Re: Move Calculated Dimension to Script

kishoreravi1983 — Mon, 23 Oct 2017 08:28:21 GMT

Thanks a Lot! it was very helpful.

Regards,

Ravi