Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

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

Edit:

sum({<[Sales Status]>}

[Quantity on Hand] * $(VComponent_Qty))

View solution in original post

7 Replies
Anonymous
Not applicable
Author

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

Edit:

sum({<[Sales Status]>}

[Quantity on Hand] * $(VComponent_Qty))

PrashantSangle

Hi,

use below :

(sum({<[Sales Status]>}

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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
Author

Hi

Sorry, the picture did not attach.  see below.

kind regards

Nayan

SOH _test.png

Anonymous
Not applicable
Author

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
Author

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