Empty total for expression in pivot table.

[#1]

Hello,

My problem basically is, that my custom expression, which is divided by quarter dimension returns no total.

(as code tells which of the values must be shown for each quarter)

I created a very simple application to explain what issue is:

I load this sample data:

LET vCurrentQuarterNo = 3;

LOAD
Division,
Quarter,
Value
INLINE [
    Division, Quarter, Value
    CARS, Q1-2014, 10
    CARS, Q2-2014, 15
    CARS, Q3-2014, 12
];


LOAD
Division,
Quarter,
QuarterNo,
Plan,
Forecast
INLINE [
    Division, Quarter, QuarterNo, Plan, Forecast
    CARS, Q1-2014, 1, 15,16
    CARS, Q2-2014, 2, 20,21
    CARS, Q3-2014, 3, 25,22
    CARS, Q4-2014, 4, 30,23
];

Then I create basic pivot table with Division and Quarter as dimensions and two expressions

1) Plan

=sum(Plan)

2) Value

=IF(QuarterNo < vCurrentQuarterNo,
  sum(Value),
IF(QuarterNo = vCurrentQuarterNo,
  sum(Forecast),
IF(QuarterNo > vCurrentQuarterNo,
  column(1))))

The result and the problem are shown below:

The problem is, that TOTAL column for value is not working...

What changes do I need to do to make this work?

Application in attachment as well.

BR,

Kuba

[#2]

Can anyone help with this issue?

[PrashantSangle]

Hi,

Change your value Expression to

IF(QuarterNo < vCurrentQuarterNo,

sum(Value),

IF(QuarterNo = vCurrentQuarterNo,

sum(Forecast),

Column(1)))

Regards

[tresesco · Accepted solution]

Re-write the formula, like:

=Sum(

IF(QuarterNo < vCurrentQuarterNo,

Value,

IF(QuarterNo = vCurrentQuarterNo,

Forecast,

IF(QuarterNo > vCurrentQuarterNo,

Plan)))

)

PFA

[#5]

Thanks to both of you! Both solutions work Second one will suit better for my final solution.