topic Re: Totals on Chart Straight Table not adding up in QlikView

Totals on Chart Straight Table not adding up

Mon, 20 Apr 2015 12:10:35 GMT

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

Re: Totals on Chart Straight Table not adding up

Anonymous — Mon, 20 Apr 2015 12:25:15 GMT

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

PrashantSangle — Mon, 20 Apr 2015 12:29:22 GMT

Hi,

use below :

(sum({<[Sales Status]>}

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

Regards

Re: Totals on Chart Straight Table not adding up

jonathandienst — Mon, 20 Apr 2015 12:31:24 GMT

The SOH expression:

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

returns the correct results

HTH

Jonathan

Re: Totals on Chart Straight Table not adding up

Mon, 20 Apr 2015 13:28:11 GMT

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

Re: Totals on Chart Straight Table not adding up

Mon, 20 Apr 2015 13:42:07 GMT

Sorry, the picture did not attach. see below.

kind regards

Nayan

Re: Totals on Chart Straight Table not adding up

Anonymous — Mon, 20 Apr 2015 13:42:34 GMT

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.

Re: Totals on Chart Straight Table not adding up

Mon, 20 Apr 2015 13:49:39 GMT

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