Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Empty total for expression in pivot table.

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:

1.png

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Re-write the formula, like:

=Sum(

IF(QuarterNo < vCurrentQuarterNo,

Value,

IF(QuarterNo = vCurrentQuarterNo,

Forecast,

IF(QuarterNo > vCurrentQuarterNo,

Plan)))

)

PFA

View solution in original post

4 Replies
Not applicable
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
tresesco
MVP
MVP

Re-write the formula, like:

=Sum(

IF(QuarterNo < vCurrentQuarterNo,

Value,

IF(QuarterNo = vCurrentQuarterNo,

Forecast,

IF(QuarterNo > vCurrentQuarterNo,

Plan)))

)

PFA

View solution in original post

Not applicable
Author

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