KPI to straight table conversion - corell

[qlikeers]

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)
)

[nevopotokcloudinary]

Hi — yes, this is a common challenge when converting a KPI expression into a straight-table context. The root issue is that your KPI:

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

works fine when it’s evaluated globally (for the whole set), but when placed in a table with a dimension like ProductLineID, the aggregation context changes and the correlation no longer computes as you expect for each row.

To correct this:

• Keep the dimension as ProductLineID.

• Inside your measure use Aggr() with the dimension explicitly (both the row dimension and the ‘fixed’ product line you’re comparing against).

• Ensure your second part (the fixed product line e.g. PL_2) is not impacted by the dimension (you may need to use {<ProductLineID={'PL_2'}>} in your set analysis to lock that side).

• Use TOTAL or ALL modifiers if needed to avoid the dimension filtering the aggr on the fixed side.

That way, for each ProductLineID row you get the correlation against PL_2, rather than the aggregated global number.

Hope this makes sense!