Pivot Table - Calculated Measure (ignore Dimensions)

[surtex]

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

[diegozecchini]

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

[surtex]

Hey diego,

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