topic Re: Set analysis. Using monthdate to calculate stock in QlikView

Set analysis. Using monthdate to calculate stock

Thu, 22 Jan 2015 15:28:26 GMT

Hi there.

Pretty new to QS and I'd really appreciate your help.

I'm trying to calculate the stock of products labelled in group 1 by the end of each month (the dimension will show the last day of each month).

What I need to set is as follows:

Each product has a purchase date and a selling date.
For each reference, if by the end of each month the purchase date is previous to that moment and the selling date isn't, then that reference will be part of the stock.

I'm trying (unsuccessfully) something like:

count({$<group={1}, adquisition_date={"<=$(MonthEnd(?????))"}, selling_date-={"<=$(MonthEnd(?????)"}>}product_reference)

Thank you.

Re: Set analysis. Using monthdate to calculate stock

Fri, 23 Jan 2015 09:16:41 GMT

If there is something else I can add to clarify the situation or whatever needs to be considered on my side please ask.

Also, if you think my approach is wrong, I'd appreciate any comment very much.

Thanks.

Re: Set analysis. Using monthdate to calculate stock

krishna20 — Fri, 23 Jan 2015 10:51:54 GMT

Hi,

Please share the dimension you need to take and the date fields. I understood that you need to calculate MTD sales. If not please let me know what exactly you need to achieve.

Regards

Krishna

Re: Set analysis. Using monthdate to calculate stock

Fri, 23 Jan 2015 11:13:30 GMT

Hi, Krishna.

Thanks for replying.

Data available

Fields

product_reference: unique id for each product. There's only one unit of every product
purchase_date (of the product)
selling_date (of the product)
product_group: just a product classfication

Example:

product_reference	purchase_date	selling_date	product_group
29.12.2894	29/01/14		1
18.11.2895	29/01/14		3
41.9.2896	30/01/14	24/04/2014	1
21.10.2897	31/03/14	02/04/2014	3
15.10.2898	31/03/14		3
12.8.2899	31/03/14	03/04/2014	1
26.4.2900	31/03/14	17/11/2014	1
41.9.2901	31/03/14	24/04/2014	4
26.9.2902	31/03/14	07/04/2014	1
42.8.2903	31/03/14		3
15.8.2904	31/03/14		3
15.9.2905	31/03/14	02/04/2014	4
18.7.2906	31/03/14	26/05/2014	5
14.8.2907	31/03/14		2
15.8.2908	31/03/14		3
41.7.2909	31/03/14		4
15.10.2910	31/03/14		2

Note: those products with blank selling date have not still been sold (to the present date), so they're part of the stock.

Note 2: for example, product 41.9.2896, in line 3 would be part of the stock on 31/01, 28/02 and 31/03 but not on 30/03, because it was sold the 24th of April.

Dimension

end_month

31/01

28/02

31/03

30/04

31/05

30/06

31/07

31/08

30/09

31/10

30/11

31/12

31/01

28/02

31/03

30/04

31/05

30/06

What I'm looking for

A graph showing different lines, each one related to a product_group and the variation of product units through time.

Example:

Thanks anyway!

Re: Set analysis. Using monthdate to calculate stock

krishna20 — Fri, 23 Jan 2015 13:12:40 GMT

Hi ,

Are you focusing to show the counts for Product_Reference in the condition Product_group =1 only or for all products?

Because, you have mentioned before like this

count({$<group={1}, adquisition_date={"<=$(MonthEnd(?????))"}, selling_date-={"<=$(MonthEnd(?????)"}>}product_reference)

Regards

Krishna

Re: Set analysis. Using monthdate to calculate stock

Fri, 23 Jan 2015 13:34:39 GMT

Please don't consider my initial statement because, due to my lack of knowledge, I've surely written something incorrect.

What I'd want to plot is what I explain in my reply:

One line for each group, showing what the stock was for each group at the end of each month (in the image posted there's only one line, as an example).
And the stock is the addition (counting) of all the references of the same group, considering its purchasing and selling dates as explained before.

Thanks.