Hi all,
I'm facing an performance issue in my dashboard. I'm explaining it below.
What I have?
1. Fact table data size: 20 lakhs records.
2. Build Calendar using the concept found in this blog post- https://qlikviewcookbook.com/2015/05/better-calendar-scripts/
3. Calendar Master is connected to Fact table by DateKey.
4. I have 4 buttons in my dashboard. "Last 12 Months", "Last 9 Months", "Last 6 Months", "Last 3 Months".
5. I'm showing Count of measure in a straight table.
6. Straight table has Month-Year as dimensions and one expression.
Requirements:
1. On click of the buttons, the charts should filter date range and update chart values.
e.g. standing in today's date (9-Mar-2017), when I select 2016 and "Last 12 Months button", the charts should show values from
01-Jan-2016 to 31-Dec-2016. But if 2017 is selected it should show values from 01-Mar-2016 to 28-Feb-2017, i.e. it will always show 12 full months.
2. If I select any Month or Quarter the chart should show values for that month or quarter only.
My solution:
1. For each button I've created trigger (External -> Set Variables).
2. Wrote below expression in chart-
If(GetSelectedCount(Month) =0 and GetSelectedCount(Quarter)=0,
Pick(Match(vDateRange, 'Last_12_months', 'Last_9_months', 'Last_6_months', 'Last_3_months'),
Sum( {<TranDate={">=$(vLast_12_Months_Start_Date)<=$(vLast_12_Months_End_Date)"}, Year=, Month=, Month_Year=, Day=>} <Measure>),
Sum( {<TranDate={">=$(vLast_09_Months_Start_Date)<=$(vLast_09_Months_End_Date)"}, Year=, Month=, Month_Year=, Day=>} <Measure>),
Sum( {<TranDate={">=$(vLast_06_Months_Start_Date)<=$(vLast_06_Months_End_Date)"}, Year=, Month=, Month_Year=, Day=>} <Measure>)
Sum( {<TranDate={">=$(vLast_03_Months_Start_Date)<=$(vLast_03_Months_End_Date)"}, Year=, Month=, Month_Year=, Day=>} <Measure>)),
Sum(<Measure>)
Problem:
Although the above expression is giving correct output but when I'm opening the dashboard the chart is opening very slowly. Waiting time 15-20 secs, sometimes even more. Remember I've 20 lakhs records in the fact. Guide me to increase the performance. If any change in the script can be better option then it is fine for me.
Any help will be appreciated. Its very urgent. Please help.
Thanks in advance.
Joy
