Stock coverage calculation

[#1]

Hi All,

I am new to qlikview, so I am asking for your help to help me solve the following issue :

I have a requirement to calculate stock coverage for any month of year month taking into consideration the following:

data set:

Product	Brand	Country	Stock Value	Budget Sales	Year	Month
X	A	US	10	5	2015	1
Y	A	US	20	6	2015	1
X	A	US	15	7	2015	2
Y	A	US	25	8	2015	2

I want to create a bar chart that shows stock coverage expressed in months of average monthly budgted sales.

I have different filters in my chart : Year / Month / Brand / Product / Country.

the formula is :  Stock value / (Annual budgeted sales/12).

I want the chart to show for any selected item in the filter, the following:

when Year filter = 2015, month = 2, Brand = A, Product = ALL then

Stock coverage  = stock value (for year=2015 month =2 Brand =A, Product = ALL) / [(Annual budgeted sales  (for year=2015 month =2 Brand =A, Product = ALL) / 12].

thank you.

Regards.

Toufik

[jagan]

Hi,

Try this expression

=Sum(StockValue)/(Sum(BudgetSales) / 12)

Hope this helps you.

Regards,

Jagan.

[#3]

Hi Jagan,

thank you for your reply, the expression you provided me is almost what I was looking for, in fact I need (Sum(BudgetSales) /12) to return the Year Budgted sales /12 where the year is the year set in the filter no matter the month selected in the filter.

Could you please suggest how to achive that .

again thank you for your help.

Regards.

Toufik

[jagan]

Hi,

Try this

(Sum({

[kangaroomac · Accepted solution]

Hi Toufik,

This may do the trick:

Sum({$<Month=>}BudgetSales)/12

[#6]

Hi Ian,

Thank you very much, it is working.

So if my understanding is correct the {$<Month=>} means sum budget sales no matter what month filter is.

Thanks a lot everyone for your contribution.

Best Regards.

Toufik

[jagan]

Hi,

Seems expression is truncated in my previous post, use below expression

Sum({<Month=>}BudgetSales)/12

Where {<Month=>} will ignore the selections in the Month field. 

If you got the answer please close this thread by giving correct and helpful answers.

Regards,

Jagan.

[kangaroomac]

No worries Toufik, and yes it does (selections made on the Month field are ignored).