1 Reply Latest reply: Mar 15, 2016 4:07 PM by Nicole Smith RSS

    Chart Pivot Table Missing Value Charater Type

    Lokesh Surana

      Hi

       

      I have Pivot table.

      4 Dimension

      1 expression (number data type)

       

      Now i entered 0 in missing cell property in presentation tab.

      This 0 is treated as charterer data type. All though my expression is number data type.

      Now if i am exporting to excel it showing me half value in number and half value in charterer.

       

      This is completed mess my excel calculation.

       

      Please guide.

       

      Regards,

      Lokesh

        • Re: Chart Pivot Table Missing Value Charater Type
          Nicole Smith

          Instead of populating the zero values using the missing cell property, you can try wrapping your expression in alt() (it acts like isnull() or coalesce() in SQL):

          alt(sum(YourField), 0)

           

           

          alt(case1[ , case2 , case3 , ...] , else)

          The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

           

          Example:

          alt( date#( dat , 'YYYY/MM/DD' ),

          date#( dat , 'MM/DD/YYYY' ),

          date#( dat , 'MM/DD/YY' ),

          'No valid date' )

           

          Will test if the field date contains a date according to any of the three specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text 'No valid date' will be returned (without any valid number representation).