Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a report which is taking very long to get results:
I have added the following dimensions:
1.=if(MONTH_NUMBER=calendar_month_num and calendar_year=YEAR_NUMBER , Monthly_Budget)
2.=if(MONTH_NUMBER=calendar_month_num and calendar_year=YEAR_NUMBER,LY_Monthly_Budget)
3.=if(MONTH_NUMBER=calendar_month_num and calendar_year=YEAR_NUMBER,MTD_Percent_To_Budget)
4.=if(MONTH_NUMBER=calendar_month_num and calendar_year=YEAR_NUMBER,LY_MTD_Percent_To_Budget)
I have selected the option suppress when value is null but it takes like 2-3 mins to get the results.Pls suggest any performance improving techniques.Enclosed is my data model and report.
Thanks,
Swetha
Hi
If you have Master Calendar table which contains all date dimensions, you can flag all last days of month with a flag in data loading script (for example IF(DAY(DateField+1) = 1, 1, 0) AS LastDay). Then you can use set analysis to limit just those records where corresponding record in Master Calendar has LastDay=1. You can use expression similar to this:
SUM({$<LastDay = {1}>} SomeAmountField)
Set analysis functionality is very powerfull in such situations, when you want to redefine selections for particular expression.
Cheers
Darius
Hi Darius,
Set analysis did work for me as you have suggested initially and the performnace is also excellenet.
But I need the data enclosed in doc1(pls see the first screenshot)but am getting differently if use set analysis (pls see scrrenshot2).
Thanks,
Swetha