Performance tuning - aggregating tables and average values

[rigosong]

Hi all, 

currently I'm encountering performance issues with one of our QlikView applications, due to the amount of data.

Situation (simplified) - Table with date and value columns:

Date	Value
01.01.20	1,356
01.01.20	2,121
01.01.20	1,640
01.01.20	1,443
02.01.20	1,495
02.01.20	1,881

User Need: Calculate average value depending on picked date range.
Eg. user picks date range 1.1.20 - 2.1.20 and should get the result 1,656. 

To get this done with a table and expression is straight forward. Date as Dimension and AVG() Expression for Value. 

However, due to the massive amount of data the Qlikview application takes for ever loading and processing user inputs. 

My thought was to transform the original tables into already aggregated tables where the values are grouped by date. The table size would be reduced imensly with this.

This idea with the above example:

Date	Avg.Value
01.01.20	1,640
02.01.20	1,688

The Problem with this: if the user picks again the date range 1.1.20 - 2.1.20 the result for average value would be  1,664 but it should be 1,656.

So, how would you tackle this problem?

[Arthur_Fong · Accepted solution]

Try this:

Script:

Raw:
load * inline [
Date	Value
01.01.20	1356
01.01.20	2121
01.01.20	1640
01.01.20	1443
02.01.20	1495
02.01.20	1881
](delimiter is '	');
let vRow=NoOfRows('Raw');

NoConcatenate
Data:
load Date,'$(vRow)'as RowNum,sum(Value) as Value
resident Raw
group by Date;

drop table Raw;
exit script;

[jonathandienst]

If the by date aggregated table contains the total value AND the number of entries for the date, you can get the original average by summing the totals and dividing by the sum of the entry counts.

[rigosong]

Thanks @Arthur_Fong , this works 🙂

[rigosong]

Thanks @jonathandienst ! Same approach as @Arthur_Fong and it works 🙂