Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikeers
Creator II
Creator II

KPI to straight table conversion - corell

We have a data model in Qlik Sense that contains a lot of records:
- CustomerID – customer number
- CustomerName – customer name
- OrderID – order number
- ProductLineID – product line (grouped products by category)
- ProductLineName – product line name
- SalesQty – number of units sold
- SalesPrice – sales price
- SalesValue – sales value, as SalesQty* SalesPrice

How to create a simple table with a column that contains the correlation of each ProductLineID row with a specific, single, selected ProductLineId in terms of SalesValue and taking into account OrderID.

Actually, the idea is to have a single KPI written like this:

Correl(
Aggr( Sum({<ProductLineID={'PL_1'}>} SalesValue), OrderID ),
Aggr( Sum({<ProductLineID={'PL_2'}>} SalesValue), OrderID )
)



Convert it into a simple table, where the dimension in the first column will be ProductLineID and for each record/value in ProductLineID it will calculate the correlation with 'PL_2', saved "hardcoded".

So, example - I have the value 0.2 for the KPI given above, after creating the table, in the row for 'PL_1' it should show 0.2.

This, as a measure, and ProductLineID as a dimension, shows numbers, but incorrectly. It only counts correctly when I make the 'PL_1' selection.

Correl(
    Aggr(Sum(SalesValue), OrderID, ProductLineID),
    Aggr(Sum({<ProductLineID={'PL_2'}>} SalesValue), OrderID)
)

 

0 Replies