topic Re: Long If Else in chart dimension in QlikView

Long If Else in chart dimension

Tue, 16 Feb 2016 22:03:33 GMT

Hello,

I am trying to show Fiscal quarter and calendar quarter and fiscal year and calendar year at the same time in same chart. Please see an attached image.

I am using variables for this selections. Please see my long calculated dimension below:

=if(vTime = 'Month' and vMonthType = 'Calendar Month',UnitMonthYearAsOf,if(vTime = 'Quarter' and vQuarterType = 'Calendar Quarter',if(UnitQuarterYearAsOf like 'Jan*','Q1 ',if(UnitQuarterYearAsOf like 'Apr*','Q2 ',if(UnitQuarterYearAsOf like 'Jul*','Q3 ',if(UnitQuarterYearAsOf like 'Oct*','Q4 ')))) & Year(UnitQuarterYearAsOf),if(vTime = 'Year' and vYearType = 'Calendar Year',UnitYearAsOf,if(vTime = 'Quarter' and vQuarterType = 'Fiscal Quarter',if(UnitFiscalShipQuarterYearAsOf like 'Feb*','Q1 ',if(UnitFiscalShipQuarterYearAsOf like 'May*','Q2 ',if(UnitFiscalShipQuarterYearAsOf like 'Aug*','Q3 ',if(UnitFiscalShipQuarterYearAsOf like 'Nov*','Q4 ')))) & Year(UnitFiscalShipQuarterYearAsOf ),if(vTime = 'Year' and vYearType = 'Fiscal Year',UnitShipFiscalYearAsOf)))))

Everytime I try to change the selection chart takes a long time to load.

All of these fields are coming from different tables. UnitMonthYearAsOf, UnitQuarterYearAsOf, UnitYearAsOf, etc

If someone can please help me, I would greatly appreciate it.

Thank you,

Parth Shah

Re: Long If Else in chart dimension

Oleg_Troyansky — Tue, 16 Feb 2016 23:16:58 GMT

Hi Parth,

you certainly shouldn't calculate fiscal Months and fiscal Quarters on the fly in your calculated dimensions. Instead, add a Master Calendar table in your data model and calculate all date-related fields there. It will help you improve the run-time performance tremendously!

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Re: Long If Else in chart dimension

marcus_sommer — Wed, 17 Feb 2016 08:48:41 GMT

I agree with Oleg that calculated dimensions are the worst case from a performance point of view. Here you find many informations about How to use - Master-Calendar and Date-Values.

- Marcus

Re: Long If Else in chart dimension

Wed, 17 Feb 2016 18:17:43 GMT

Thank you Oleg and Marcus. I tried to implement the similar approach. The reason I couldnt implement master calendar is because all of these fields are from different tables I created in order to make it work with cumulative view. And also changed couple things in GUI. Still it is happening. Please find the attached picture.

Please also see below for calculated dimension field.

=if(vTime = 'Calendar Month',AssetMonthYearAsOf,if(vTime = 'Calendar Quarter',AssetCalQuarterYearAsOf,if(vTime = 'Calendar Year',AssetYearAsOf,if(vTime = 'Fiscal Quarter',AssetFisQuarterYearAsOf,if(vTime = 'Fiscal Year',AssetShipFiscalYearAsOf)))))

The key is if a person changes the view from Calendar Month to Fiscal Quarter -> it should change the view accordingly. And I am handling this through variable vTime.

Please let me know if there is any alternative of this if else condition.

I appreciate your help.

Thank you,

Parth

Re: Long If Else in chart dimension

marcus_sommer — Wed, 17 Feb 2016 19:16:53 GMT

Especially if you have multiple dates from various tables do you need a script-solution - here is an excellent explanation how to handle it: Canonical Date and on them you could connect a fiscal calendar: Fiscal Year. More informations could you find within the link from above.

- Marcus