Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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
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
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