topic Re: Empty total for expression in pivot table. in QlikView

Empty total for expression in pivot table.

Mon, 29 Sep 2014 11:21:47 GMT

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: Empty total for expression in pivot table.

Wed, 01 Oct 2014 05:49:40 GMT

Can anyone help with this issue?

Re: Empty total for expression in pivot table.

PrashantSangle — Wed, 01 Oct 2014 06:00:30 GMT

Hi,

Change your value Expression to

IF(QuarterNo < vCurrentQuarterNo,

sum(Value),

IF(QuarterNo = vCurrentQuarterNo,

sum(Forecast),

Column(1)))

Regards

Re: Empty total for expression in pivot table.

tresB — Wed, 01 Oct 2014 06:02:26 GMT

Re-write the formula, like:

=Sum(

IF(QuarterNo < vCurrentQuarterNo,

Value,

IF(QuarterNo = vCurrentQuarterNo,

Forecast,

IF(QuarterNo > vCurrentQuarterNo,

Plan)))

)

PFA

Re: Empty total for expression in pivot table.

Wed, 01 Oct 2014 06:25:55 GMT

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