4 Replies Latest reply: Jun 7, 2016 2:35 AM by Pekka Laaksonen RSS

    Exclude selection in IF statement

    Pekka Laaksonen

      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.