Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Re-write the formula, like:
=Sum(
IF(QuarterNo < vCurrentQuarterNo,
Value,
IF(QuarterNo = vCurrentQuarterNo,
Forecast,
IF(QuarterNo > vCurrentQuarterNo,
Plan)))
)
PFA
Can anyone help with this issue?
Hi,
Change your value Expression to
IF(QuarterNo < vCurrentQuarterNo,
sum(Value),
IF(QuarterNo = vCurrentQuarterNo,
sum(Forecast),
Column(1)))
Regards
Re-write the formula, like:
=Sum(
IF(QuarterNo < vCurrentQuarterNo,
Value,
IF(QuarterNo = vCurrentQuarterNo,
Forecast,
IF(QuarterNo > vCurrentQuarterNo,
Plan)))
)
PFA
Thanks to both of you! Both solutions work Second one will suit better for my final solution.