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.
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!
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.
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.