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
