Previous N months date selection, performance issue

joydipp1988 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Previous N months date selection, performance issue

settu_periasamy — Wed, 08 Mar 2017 20:00:48 GMT

May be try this..

Instead of Single expression, create 4 expression for each Criteria (Last 12 Months, Last 9 months, etc.)

create 4 more variables and assign to the button.