3 Replies Latest reply: Feb 3, 2016 9:44 AM by Michael Denman RSS

    Nested If statements in straight table

    Michael Denman

      Hi all, I am trying to set up a straight table that has multiple dimensions based on a field called Time. Basically if I choose Time = 'Year' then I want Month and Quarter calendar functions to be ignored. Also, if I choose Time='Quarter' I want Month to be ignored.

       

      This expression works perfectly in a text box, but it defaults to the last Then statement in the straight table, in bold. Because of this, no matter what selection I make in Time, Month and Quarter are being excluded, which is not what I want.

       

      This is  aggregated value

      =if(Time = 'Month', Sum({$< Metric = {'New Business USD'}>}  Value),

        if(Time = 'Quarter', Sum({$< Metric = {'New Business USD'}, Month=>}  Value),

        Sum({$< Metric = {'New Business USD'}, Month=, Quarter=>}  Value)))

       

      Point to point

      =if(Time = 'Month', FirstSortedValue({<Metric = {'30+%'}>} Value, -MonthYear),

        if(Time = 'Quarter', FirstSortedValue({<Metric = {'30+%'},Month=>} Value, -MonthYear),

        FirstSortedValue({<Metric = {'30+%'},Month=,Quarter=>} Value, -MonthYear)))

       

      Example with nested if's attached.

       

      Thanks in advance!