Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cprasad111
Contributor II
Contributor II

Correl and Agg function for dynamic correleation calculation

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

0 Replies