Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need your help. I have a pivot table in which I have to show up the Actual and the Forecast prices for some materials for each month. If I select from the list box the current year, I'll need to visualize the Actual Prices up to the last month (but this is not a problem because the actual prices are loaded until the previous month) and for the next months the Forecast Prices up to the end of the year (but the forecast prices are loaded for entire year). If I select a past year, I'll visualize only the Actual Prices.
In the attached file you can see the layout I want.
I was thinking to use Conditional Expression on the Forecast prices and use a condition like Month >= CurrentMonth but I'm very new on QV, so I need your help.
Thank you
Hi Cristina ! (spanish?)
Supossing that "Month" is a field with the number of Month, i guess that your "current" month is the month you have selected.
The following expression will give you the actual_price until your selected month if you are selecting the last year, and then forecast_price. If your selected year is not the last, only shows actual_price:
if(Year<Max({1}Year),
Sum(actual_price),
sum ({<Month={"<$(=Max(Month))"}>} forecast_price) +
sum ({<Month={">=$(=Max(Month))"}>} actual_price)
)
Try sum(alt(ACTUAL,FORECAST))