4 Replies Latest reply: Nov 19, 2012 11:01 AM by Diego Adum

# Show sales and forecast data in the same line

Hi there!!
The idea is to show the sales amounts for the already past months and for the remaining months the sales forecast. I'm using this formula:
=
if([Fiscal Period]<=cCurrFiscalPeriod,

if(varCurrType = 'L',

Sum(CustProd_SaleAmtLocalCurr)/1000,

Sum(CustProd_SaleAmtUSCurr)/1000

)

,

if(varCurrType = 'L',

Sum(ForecastAmtLocalCurr)/1000,

Sum(ForecastAmtUSCurr)/1000

)

)
cCurrFiscalPeriod is the variable on which I have the current already closed month.
My current issue about this way is the sum on the table. It seems the table is calculating only sales values or forecast values. Anyway, from 2012, periods 1, 2 and 3 has already past, so should be calculated only sales giving the right result, but I'm lost in this area.
Here is the table I got folow the formula showed before.
Fiscal Period123Total
2012 Actual / Forecast 11+11629168816751202

Thanks in advance for any guidance!

PS: the varCurrType variable is just for showing local currency or dolars figures.

• ###### Re: Show sales and forecast data in the same line

I'm not sure if this will help you or not, but I have attached an example of using an if() expression to determine whether to use Sales or Forecast as the data. Then you can use a similar expression in the line color to show which is which.

• ###### Re: Show sales and forecast data in the same line

Rebecca, thank for your suggestion but if you turn your chart into table and add a total for the values, you will face the same issue as me. Your total (according to your numbers) should be 29683, but the table in your example is showing 27064. May be from here you can figurate out what is missing in order to get a total which represent the values in the table.