Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Hi,
Here is the QV file.
Thanks
Saran.
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
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
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
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
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