Exclude selection in IF statement

[#1]

Hi,

I have a chart where I am summarizing revenue and forecasts per year and month. My master calendar is divided in half: first half are dates until today and second half are dates after today. This is due to requirements set by other reports in my application. Division is made by hidden field and locked selection.

Now, in the new report I am making I have to summarize all amounts, no matter if they are in past or future (actual revenue is in past and forecasts might be in the future). This is easy, I'll just exclude the calendar divider in set analysis:

sum({<%CalendarDivider=>}ActualAndForecastAmount)

However, other requirement I have, is that I have to color code actual revenue and forecasted amounts. My table is formed in following way:

1. Revenues are loaded and last month where we have recorded revenue is picked into variable.

2. Rest of table is filled with forecasted months greater last month where we have recorded revenue.

therefore color coding table cells seems straight forward:

if(Month>$(vLastActualRevenueMonth), Yellow(), LightGreen()

However, I have the calendar which messes things up! Color coding this way doesn't work if I don't ignore selection from hidden divider field like in did in summarize expression. I tied to do something like this:

if( {<%CalendarDivider=>} Month>$(vLastActualRevenueMonth), Yellow(), LightGreen()

But of course it doesn't work. Is there a way of excluding that selection in if-statement? I'd rather not use actions.

[settu_periasamy]

Not sure. But try this..

=if( Only({<%CalendarDivider=>} Month)>$(vLastActualRevenueMonth), Yellow(), LightGreen())

[qliksus]

If you are using Month column as dimension then the IF condition process it Row by Row so you can use something like

If ( Sum({<%CalendarDivider=>} MonthNum ) >$(vLastActualRevenueMonthnum), Yellow(), LightGreen())

Assuming you have a Monthnum column

[#4 · Accepted solution]

Hi,

Thanks for the suggestions. I actually got this working before my question was approved with this:

if(max({<%CalendarDivider=>}YearMonth)>vMaxActualRevenueMonth, Yellow(), LightGreen())

This colors the months with actual recorded revenue green and forecasted months yellow. Problem with this is that also pivot table totals are colored. I wouĺd prefer totals to be black on white but I don't think there's anything I can do about that.

Solution suggested by qliksus seems quite similar to what I did so I have no doubt that it would't work as well. However, since I haven't tried it, I'll mark my own solution as correct answer as I know that it produces result I was looking for.

[#5]

Actually there is a solution for total rows as well.

Using RowNo() and ColumnNo() functions you can format total rows:

=if(rowno()=0 or ColumnNo()=0

    ,white()

    ,if(max({<%CalendarDivider=>}YearMonth)>vMaxTurnoverVsRevenueForecastPeriod, Yellow(), LightGreen())

)

)