Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Long If Else in chart dimension

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

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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!

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Not applicable

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

Capture.PNG

MVP & Luminary
MVP & Luminary

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