Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Try this expression
=Sum(StockValue)/(Sum(BudgetSales) / 12)
Hope this helps you.
Regards,
Jagan.
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
Hi,
Try this
(Sum({
Hi Toufik,
This may do the trick:
Sum({$<Month=>}BudgetSales)/12
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
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.
No worries Toufik, and yes it does (selections made on the Month field are ignored).