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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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