Pivot table issues

[#1]

Hi there,

I am an end user of Qlikview and am struggling to create a view for management with regards to forecasting. Could you please point me in the right direction of how I am able to solve these issues?

1) Displaying a value of 0 even though there is no data within the filters

This is like the example in the Qlikview reference Manual book 1 page 160. I want to be able to produce a pivot table with months on the top and product on the side and to view sales by month. If I select an item and the item has sold in Jan, Feb and April, I want to still show that the item sold 0 in March. Is this possible?

2) Displaying stock and how it is affected by SO and PO by month

I want to be able to provide a figure in each period of the est amount of stock I have. I have a fact table called stock and as an example, item A has 1000 pcs of stock available currently. I want to be able to use this value to calculate est stock per month.

E.g.

Sales Order			Purchase Order
April	200		May	100
May	300
July	300

I need to display this as output:

		April	May	June	July
Item A	Opening Stock	1000	800	600	600
	Sales Order	200	300	0	300
	Purchase Order	0	100	0	0
	Est. Stock	800	600	600	300

I have looked at the ABOVE function but I do not think that this would provide the solution? I am guessing that a cumulative function would do but I am unsure of the correct syntax to do this.

As you can see, I am a novice at this and am trying to get my head round this so any help from you is greatly appreciated.

Mike

[#2]

Hi Michaellee,

I think you can try for this solution. Actually u have to tune your data like the excel file format, i have used.

Try this out and let me know your comments.

Thanks

Saran.

[#3]

Hi,

Here is the QV file.

Thanks

Saran.

[#4]

Hi Saran,

Thanks for your help!

The data I am to load is from multiple tables. 2 fact tables (Item table, Stock table) and 2 dimension tables (SO and PO tables) and I would like to calculate the est stock on the fly. I have attached the qvw file that I have created and if you have any pointers, please let me know!

Thanks

Mike

[Oleg_Troyansky · Accepted solution]

Michael,

see an attached solution. You need to use a combination of functions ColumnNo() and Above(), to calculate estimated stock balance.

While looking at your application, I noticed a couple of other issues that I'd recommend taking care of:

1. You have a synthetic key that needs to be resolved.

2. You are using sum(IF...) expressions... If your real data is large, you need to avoid those expressions (see my Wiki about it).

Since you are an end user, I'd say - you need to engage your developer to clean up some of those technical issues.

cheers,

Oleg

[#6]

Hi Oleg,

Thanks for the code! I have tested this with some data and I find something strange..

We have outstanding SO where they are before today (I know its bad data as the customer service peeps should have amended it..) and when I use the code that you provided, it produces the values in the qvw. Could you tell me why this would happen?

Many thanks for you help,

Mike

[Oleg_Troyansky]

Michael,

well, to your question - QlikView doesn't know that SO with past dates is "bad data", so it will generate the same logic. You can prevent it by either filtering the data out in the script (if it's truly garbage), or applying a condition in the chart - using Calculated Dimensions or Set Analysis on the expressions.

I just noticed a little glitch in my suggested formula - it works well within a single year, but not across several years.

Instead of function ColumnNo(), use ColumnNo(TOTAL), otherwise, every new year will bring the Inventory back to the current value.

cheers,

Oleg

[#8]

Oleg,

Thanks for the pointer and i checked in the reference manual and I would also need a TOTAL in the before() function. My code now looks like this

if(ColumnNo(total)=1, Sum([Stock Quantity]), before(total [Est Stock]))

This works great when I select one item at a time but when I clear all filters, the opening stock did not populate...

Mike