Correl and Agg function for dynamic correleation calculation

cprasad111 — Mon, 25 Feb 2019 03:47:49 GMT

I've a transaction data set as below. There are around 5 products ( say from A to E). Every product can have multiple transactions on same date

Raw Data

TransactionID, Date, Product, Amount
1, 15/10/2018, A, 100
2, 15/10/2018, A, 200
3, 16/10/2018, A, 102
4, 16/10/2018, A, 250
5, 15/10/2018, B, 99
6, 15/10/2018, B, 211
7, 16/10/2018, B, 122
8, 16/10/2018, B, 280

Approach 1: not working

Created two tables RawData and RawDataCopy both having Date as common field

Pivot table - Row: Product, column: ProductCopy

Measure: Correl(Aggr(Sum(Amount),Date), Aggr(Sum(AmountCopy),Date))

Approach 2: Working but it's static as need to prepare the table before hand in the script

SummaryTable:

Load Date, Product, sum(Amount) as sum_Amount group by Date, Product

Created a SummaryTableCopy table with Date as the common columns

Pivot table - row: Product, column: ProductCopy

Measure: Correl(sum_Amount, sum_Amount_Copy)

Question

Is there any way to get the Approach 1 works as I have other dimensions like Product, Sub_Product, Locations, Type etc. and the correlations I need can be dynamic like below and don't want to create multiple summary tables for each

1. Product <-> Product level

2. Product-Locations <-> Product-Locations level etc

I've seen another post on correlation but it's the approach 2 (which it's working for me as well) and not the one which I require

url: https://community.qlik.com/t5/QlikView-App-Development/Dynamically-finding-correlations/td-p/1248596

topic Correl and Agg function for dynamic correleation calculation in App Development

Correl and Agg function for dynamic correleation calculation