topic Re: Pivot Table - Calculated Measure (ignore Dimensions) in Qlik Answers

Pivot Table - Calculated Measure (ignore Dimensions)

surtex — Tue, 11 Feb 2025 09:43:29 GMT

Hello together,

I have the following problem. I have a pivot table with different dimensions in row:

1) Inc_Cost_Indicator: Income, Costs, EBIT

2) Cost Element LVL 4

3) Cost Element LVL 3

4) Cost Element LVL 2

5) Cost Element LVL 1

In the columns are different KPI's like:

If(Income_Cost_Indicator = 'Income',

SUM({<Year_CC = {"$(=Right(DateFilterChip,4))"}, Month_CC={"$(=Num(Left(DateFilterChip,2)))"} ,Value_Type_Code = {'020'}, DateFilterChip=>} Cost_Center_Amount)/vDenominator*(-1), //SUM(X)

If(Income_Cost_Indicator = 'Costs',

SUM({<Year_CC = {"$(=Right(DateFilterChip,4))"}, Month_CC={"$(=Num(Left(DateFilterChip,2)))"} ,Value_Type_Code = {'020'}, DateFilterChip=>} Cost_Center_Amount)/vDenominator)) //SUM(Y)

I would like to add the following condition: If(Income_Cost_Indicator = 'EBIT', SUM(X) - SUM(Y) and ignore the dimensions above.

Has anybody an idea?

Thanks and best regards

Josh

Re: Pivot Table - Calculated Measure (ignore Dimensions)

diegozecchini — Tue, 11 Feb 2025 11:16:33 GMT

Hi!
you can achieve this in Qlik by using set analysis with the {1} identifier to ignore dimensions in your pivot table.
You need to modify your formula so that when Income_Cost_Indicator is = 'EBIT', the calculation does not depend on the pivot dimensions.

so try this:
If(Income_Cost_Indicator = 'Income',
SUM({<Year_CC = {"$(=Right(DateFilterChip,4))"}, Month_CC={"$(=Num(Left(DateFilterChip,2)))"} ,Value_Type_Code = {'020'}, DateFilterChip=>} Cost_Center_Amount) / vDenominator * (-1), // SUM(X)

If(Income_Cost_Indicator = 'Costs',
SUM({<Year_CC = {"$(=Right(DateFilterChip,4))"}, Month_CC={"$(=Num(Left(DateFilterChip,2)))"} ,Value_Type_Code = {'020'}, DateFilterChip=>} Cost_Center_Amount) / vDenominator, // SUM(Y)

If(Income_Cost_Indicator = 'EBIT',
SUM({1<Income_Cost_Indicator={'Income'}, Year_CC = {"$(=Right(DateFilterChip,4))"}, Month_CC={"$(=Num(Left(DateFilterChip,2)))"}, Value_Type_Code = {'020'}, DateFilterChip=>} Cost_Center_Amount) / vDenominator * (-1)
-
SUM({1<Income_Cost_Indicator={'Costs'}, Year_CC = {"$(=Right(DateFilterChip,4))"}, Month_CC={"$(=Num(Left(DateFilterChip,2)))"}, Value_Type_Code = {'020'}, DateFilterChip=>} Cost_Center_Amount) / vDenominator
)
)
)

Re: Pivot Table - Calculated Measure (ignore Dimensions)

surtex — Tue, 11 Feb 2025 11:30:03 GMT

Hey diego,

that looks fine, but then I get the value 0...