Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Conditional Expressions in pivot table

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try sum(alt(ACTUAL,FORECAST))


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
sebastiandperei
Specialist
Specialist

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)

)

Gysbert_Wassenaar

Try sum(alt(ACTUAL,FORECAST))


talk is cheap, supply exceeds demand