Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Stock coverage calculation

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:

ProductBrandCountryStock ValueBudget SalesYearMonth
XAUS10520151
YAUS20620151
XAUS15720152
YAUS25820152


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

Tags (1)
1 Solution

Accepted Solutions
kangaroomac
Contributor II

Re: Stock coverage calculation

Hi Toufik,

This may do the trick:

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

7 Replies
MVP
MVP

Re: Stock coverage calculation

Hi,

Try this expression

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

Hope this helps you.

Regards,

Jagan.

Not applicable

Re: Stock coverage calculation


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

MVP
MVP

Re: Stock coverage calculation

Hi,

Try this

(Sum({

kangaroomac
Contributor II

Re: Stock coverage calculation

Hi Toufik,

This may do the trick:

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

Not applicable

Re: Stock coverage calculation

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

MVP
MVP

Re: Stock coverage calculation

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
Contributor II

Re: Stock coverage calculation

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