Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Total does not add up

Hi

I have a problem with a pivot table where the total do not add up.

I have a buying template and want to calculate the stock required which is  (if negative , give zero):

 

if(Average*7-[Stock on Hand]+[Sales Order]<0,0,Average*7-[Stock on Hand]+[Sales Order]) .

The only problem im having is that total of the "stock required"  is incorrect.  The total should be 10,697 and not 9768.

im aware that the solution works for a straight table.  I want it to work for a pivot table.

I have attached the Qlikview Model as well as the data (in excel)

Please can you help.

kind regards

Nayan

1 Solution

Accepted Solutions
tresesco
Not applicable

Re: Total does not add up

PFA

5 Replies
mayilvahanan
Not applicable

Re: Total does not add up

Hi

Try like this

In your expression:

=If(RowNo() = 0 or IsNull(RowNo()), Sum(if(Average*7-[Stock on Hand]+[Sales Order]<0,0,Average*7-[Stock on Hand]+[Sales Order])),

if(Average*7-[Stock on Hand]+[Sales Order]<0,0,Average*7-[Stock on Hand]+[Sales Order]))

tresesco
Not applicable

Re: Total does not add up

PFA

swuehl
Not applicable

Re: Total does not add up

You can look into the method

Sum of rows in pivot tables using adavanced aggregation

in the Help.

This will look like

=sum( aggr(

if(Average*7-[Stock on Hand]+[Sales Order]<0,0,Average*7-[Stock on Hand]+[Sales Order])

,[Component Code],[ITEM  CODE]))

for your expression.

Probably there is even an easier solution.

Not applicable

Re: Total does not add up

Hi All

Thank you for the responses.  It works on the QV Template given.  I'm going to try it out on my actual model and will let all know regarding the outcome.

kind regards

Nayan

Not applicable

Re: Re: Total does not add up

Hi Mayil

Your formula works.

However , if i use the heading names in the stock required , the formula does not work.  (see model attached).

Can you tell me why?

I change the names of the headings of the 3 columns :

ie  Average  = NewAVG

      Stock on Hand = NewSOH

      Sales Order = NewSO

In my actual buying temple, each of the columns is a complex if formula, hence i use the name of the headings in my stock required formla.

kind regards

Nayan