Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You have to place *$(vComponentQty) inside sum()
Edit:
sum({<[Sales Status]>}
[Quantity on Hand] * $(VComponent_Qty))
You have to place *$(vComponentQty) inside sum()
Edit:
sum({<[Sales Status]>}
[Quantity on Hand] * $(VComponent_Qty))
Hi,
use below :
(sum({<[Sales Status]>}
[Quantity on Hand]*$(VComponent_Qty)))
Regards
The SOH expression:
sum({<[Sales Status]>} [Quantity on Hand] * [Component Qty] )
returns the correct results
HTH
Jonathan
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
Hi
Sorry, the picture did not attach. see below.
kind regards
Nayan
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.
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