# Qlik Sense App Development

New Contributor II

## Calculate the percentage of each Month Sales against Forecast

Hi,

I am currently trying to calculate the percentage of spend for each month of the year against each other. The following table is my data set with a CurrentDateKey in a different table that defines the current month of the year that uses the same format as the DateKey.

 DateKey Store Month Sales Sales Forecast FinancialYear 072017 Store1 July 325 355 2017 082017 Store1 August 10 20 2017 092017 Store1 September 125 135 2017

The following code for my demension calculates the sales and forecast of all the months that have past cumulatively to define the percentage of sales against the forecast for the total year to date. Then defines the percentage of sales per store but just counting the stores for the related dual value.

What I want to be able to do is calculate the percentage of sales against forecast for each month against each other.

What the current code calculates =  (325+10+125) / (355+20+135) =  90.1% of expenditure against forecast

How I want the code to calculate = (325/355) + (10/20) + (125/135) = 78% of expenditure against forecast

Aggr(

if(Sum({\$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast)) > 1.5, Dual('Greater than 150%',5),

if(Sum({\$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast)) > 1.25, Dual('126% - 150%',4),

if(Sum({\$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast))  >= .75, Dual('75% - 125%',3),

if(Sum({\$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast))  >= .50, Dual('50% - 74%',2),

if(Sum({\$<FinancialYear={'2017'} >}Sales)/Sum(if(DateKey >= '072017' and DateKey < (CurrentDateKey), Sales Forecast))  < .50 , Dual('Below 50%',1),

))))),Store)

Thanks,

William

1 Solution

Accepted Solutions
MVP

## Re: Calculate the percentage of each Month Sales against Forecast

Or this

Sum(Aggr(Sum(Sales)/Sum([Sales Forecast]), DateKey, Store))

3 Replies

## Re: Calculate the percentage of each Month Sales against Forecast

you should try

Sum(X/Y)

which will first divide and then add those values.

Regards,

Kaushik Solanki

MVP

## Re: Calculate the percentage of each Month Sales against Forecast

Or this

Sum(Aggr(Sum(Sales)/Sum([Sales Forecast]), DateKey, Store))

New Contributor II

## Re: Calculate the percentage of each Month Sales against Forecast

Thanks, this worked for me.

The other answer by Kaushik does work in some instances but if a store has two forecast rows for a single month it doesn't calculate properly.