Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Totals on Chart Straight Table not adding up

Hi

In our business, we sell a component as well as the final product in which the component goes to.  Below is a screen shot of of my chart straight table showing 6 months sales and Stock on Hand (SOH). The SOH is the Item SOH multiplied by the Component Quantity.

We sell to our  branch and to customers (ie "Sell Out") .  In my table, I Ignore sales to Branch , hence the list box sales status , "Sell Out" is selected.

I am struggling to get the correct total for the column Stock on Hand (SOH) below.  I want to show SOH, even if the item is not sold.  In my example, item JKL.  

The correct total for ABC should be 23,273 & for JKL is 723,449. 

Attached herewtih is the Qlikview Model as well as the Database in Excel Spreasheet.

Please can you help

kind regards

Nayan

Sales Status.JPG

SOH.JPG

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

Re: Totals on Chart Straight Table not adding up

You have to place *$(vComponentQty) inside sum()

Edit:

sum({<[Sales Status]>}

[Quantity on Hand] * $(VComponent_Qty))

7 Replies
mov
Esteemed Contributor III

Re: Totals on Chart Straight Table not adding up

You have to place *$(vComponentQty) inside sum()

Edit:

sum({<[Sales Status]>}

[Quantity on Hand] * $(VComponent_Qty))

Re: Totals on Chart Straight Table not adding up

Hi,

use below :

(sum({<[Sales Status]>}

[Quantity on Hand]*$(VComponent_Qty)))

Regards

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

Re: Totals on Chart Straight Table not adding up

The SOH expression:

sum({<[Sales Status]>} [Quantity on Hand] * [Component Qty] )

returns the correct results

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Totals on Chart Straight Table not adding up

Hi All

Your formulas worked on this model. , however on the actual Qlikivew Model that I am working with , some of the items the SOH are multiples.

For eg, below, FBGLEMD, the SOH should be 13,174 and not 26,348. For FEEMD, it should be 11,117 and not 55,585.

Unfortunately I can’t upload my original model. Can you tell me why this happens.

The “SOH” below is the original formula I had, and the “SOH test”, I applied your scripting to it.

Kind regards

Nayan

Not applicable

Re: Totals on Chart Straight Table not adding up

Hi

Sorry, the picture did not attach.  see below.

kind regards

Nayan

SOH _test.png

mov
Esteemed Contributor III

Re: Totals on Chart Straight Table not adding up

Something is not quite right in the data model, I suspect.  It may or may not be possible to fix on the expression level.

(Very wild guess: sum({<[Sales Status]>} [Quantity on Hand] * aggr(min([Component Qty]),[Component Code],[Item Code])))

For a better help, you have to upload an example with that sort of problem.

Not applicable

Re: Totals on Chart Straight Table not adding up

Hi

Thank you for your reply.   I even tried the following formula:

 

sum

(aggr(RangeMax
(
sum({<[Sales Status]=>}

(
[Loc Qty On Hand])
*
$(vComponent_Qty))),[ITEM CODE],[Component Code]))

but does not work either.

kind regards

Nayan

Community Browser