Show sales and forecast data in the same line

[#1]

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 Period	1	2	3	Total
2012 Actual / Forecast 11+1	1629	1688	1675	1202

Thanks in advance for any guidance!

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

[#2]

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.

[#3]

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.

Again thanks for your reply.

[#4 · Accepted solution]

You just have to change the total from "expression total" to "Sum of Rows".  Here you go.

[#5]

Thanks Rebecca!! My only comment is about this is working using a Straight Table and not a pivot table due to the fact that Total Mode is not available in Pivot table. Nevertheless using the "Horizontal" check box in the presentation I can accomodate my Quarters (Your periods) in a horizontal way which is the way that we need. Again thanks a lot for your guidance and help in this topic.