Items sold vs Current stock level

[#1]

Hello QlikView fans/experts,

I'm looking for a way to present the number of items sold in a period against the current stock levels in a single tabel. In this case it is also important to see items that are not sold in a selecter period, and of course it should also be possible to see only items that belong to a specific group or supplier. I tryed different ways but I can't find a solution for me problem.

In QlikView I currently have the following tables:

Article Articlenumber Articledescription Articlegroup Suppliernumber

Transaction Transactionumber Date Time Articlenumber Items_sold Storenumber

Store Storenumber Storename Supplier Suppliernumber Suppliername

Stock Articlenumber Storenumber Stocklevel

At the moment I use the following links between these tables, don't get confused by the stated fieldnames I used other names to prevent circular loops.

Article_Articlenumber -> Transaction_Articlenumber
Article_Articlenumber -> Stock_Articlenumber
Article_Suppliernumber -> Supplier_Suppliernumber
Transaction_Storenumber -> Store_Storenumber

My end result in a QlikView dashboard should be a chart-table like this:

			Storenumber	Storenumber	Storenumber	Storenumber
	Total	Total	1	1	2	2
Articlenumber	Items sold	Stock level	Items sold	Stock level	Items sold	Stock level
123456	100	50	60	20	40	30
234567	200	100	120	40	80	60
345678	150	120	75	50	75	25
456789	175	40	90	40	85	50

In this table I used the articlenumber and storenumber as an example, it would even better if I could use articlename and storename. Every suggestion to change my QlikView datamodel or to use certain functionality in QlikView is welcome. Is there anyone that can point me in the right direction to find a solution? Thank you in advance!

regards Mark

[Anonymous]

Mark,
Unless I'm missing something essential... I'd leave two tables (not that it is absolutely necessary, but the fewer usually the better):
Article (made of Article and Supplier):
Articlenumber
Articledescription
Articlegroup
Suppliernumber
Suppliername
Transaction (made of Transaction, Store and Stock ,concatenating Transaction and Stock):
Transactionumber
Date
Time
Articlenumber
Items_sold
Storenumber
Storename
Stocklevel

As for the presentation, it looks for me like a pivot table with dimensions Articlenumber (or description) on the left, and Storenumber (or name) on top, and two expressions - sum(Items_sold) and sum(Stocklevel). Expressions on top, below the Store, totals on left.
Does it work for you?

[#3]

Michael,

Thank you for your suggestion. I don't think it will help me solve my problem, or I'm the one missing something here..
With your suggested data structure I still have the same problem when selecting a certain period based on the transaction date. A period selection will only show articles in the pivottable that are sold in that period. I also want to see the articles that are not sold, because they can also have stock, but I even want to see articles that are not sold and have zero stock. Off course I can use the "Show All Values" checkbox in the pivottable properties, but then I can't use articlegroup or supplier for selecting the articles I want to see in the table. Hope you undertand what I mean.

regards,

Mark

[Anonymous]

Now I see what I'm missing.
I'll try to get back to this tomorrow. At this moment what I think is that there should be two dimension tables - Articles, which you have already, and Date (Calendar) which you don't have yet. And, there should be generic keys in both (or maybe enough to have it only for Date in your case), to allow stock to be independent from Date selections. Transaction table stays the same in structure, but different in content - generic Date value for the Stock rows.

Addition:
I didn't plan to get back to the forums today, but just thought of something else... Probably you can stay with the simple data model, the one I described in the first reply, but instead make the expression for stock itself date-insensitive. For example, using set analysis or "total" qualifier.

[#5]

Hello Michael,

Thanks for your awnser again, I'm not exactly sure what you mean, can you maybe clarify it a litle bit. I already tryed to create a pivot tables that uses set-analysis to calculate the number of items sold for each article. By de-linking the stock and transaction table this could give the right result. But I can't get it working, the table keeps loading or does nog give a result for the items sold at all..

regards Mark

[Anonymous · Accepted solution]

Mark,
See attached - this is a way how I'd do it using the data modeling. Maybe it's simpler with set analysis, but I didn't try that.
Zip includes qvw file and "data source".

[#7]

Dear Michael,

Thank you very much for your effort. I'll try if your data model works for us, and I'll let you now the results. It certainly looks like this is the solution for our problem.

regards Mark

[#8]

Dear Michael,

In your example, what if you need to calculate on the report, not on the script,  the stock level per quarter and product , assuming you must use the last available month of each quarter.

Appreciate all your help

Kind regards