0 Replies Latest reply: Jul 24, 2014 7:15 AM by Erik Furlanis RSS

    hide dimension entries with no values when using sum TOTAL

      I have a full table, where some fields are concatenated to create keys.

      These keys are linked to a second table which has values just for some of these keys.

       

      Let say that in Table A, by concatenation I obtain 10 keys

      but in Table B I actually have values associated to only 5 of these 10 keys. Let assume these 5 keys ends with AA, meanwhile the keys with no values in table B ends with BB

       

      if I use following expression in a pivot

       

      sum( { 1<key={' *AA'}>} value )

       

      I get correct values just for those 5 keys

       

      but if I use following expression

       

      max( sum( { 1<key={' *AA'}>} TOTAL value )

       

      then the Total sum of all the 5 values is given on ALL of the ten keys.

      Previously the pivot was just showing the 5 keys with a value

       

      Is there a way to avoid to show, by using the max/TOTAL function, the keys which actually dont have a value?

       

      thank you!