Totals not calculating correctly

Fri, 21 Aug 2009 07:16:56 GMT

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

Totals not calculating correctly

disqr_rm — Fri, 21 Aug 2009 09:32:01 GMT

Try:

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

topic Totals not calculating correctly in QlikView

Totals not calculating correctly

Totals not calculating correctly