Totals not calculating correctly

[#1]

Hi

I have a formula that says if the month number (PeriodNum) is less than the current month number (as defined in a text box call vCurrentPeriodNum) then use the Actual Sales value else if the Month number => current month number then use the forecast sales value. it looks like this:

if(PeriodNum < vCurrentPeriodNum,sum(if(Year = vCurrentYear,(Sales))),sum(ForecastSales))

The Months are July = 1, Aug = 2 etc

This lets us define the current month and show actual sales for the months already completed and forecast sales for the upcoming months in a timeline.

There is also a section of the formula "Year = vCurrentYear" so that it knows to use the current years month number as the data has multiple years worth of data in it.

The problem is that I get the following result in the attached excel spreadsheet... [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/6354.Qlikview-test.xls:550:0]

The total for the forecast per the formula should be $15066 if you add the RF (Rolling Forecast) column totals but it returns $13531.

This is because as we are now in the financial period 2 (August), The July forecast amount of $866 has been replaced by the actual sales value of $2401(you can see that RF and Actual are the same) but the total is calculating off the forecast amount of $866.

i.e. $866 + $3415 + $9250 = $13531

Does anyone know of a way to get the totals to take the value in the cell?

David

[disqr_rm · Accepted solution]

Try:

sum(if(PeriodNum < vCurrentPeriodNum and Year = vCurrentYear, Sales, ForecastSales))