Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

Re: Conditional Expressions in pivot table

Try sum(alt(ACTUAL,FORECAST))


talk is cheap, supply exceeds demand
2 Replies
sebastiandperei
Valued Contributor

Re: Conditional Expressions in pivot table

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)

)

Re: Conditional Expressions in pivot table

Try sum(alt(ACTUAL,FORECAST))


talk is cheap, supply exceeds demand
Community Browser