6 Replies Latest reply: Oct 2, 2012 10:52 AM by Helen Pippard RSS

    How Do I supress Expression in a pivot table

    Helen Pippard

      Hello,

       

      I was wondering if you could kindly help me, hopefully with the syntax in my dimensions and expressions

       

      How Do I supress Median, Fractile and Longest Wait under the "Left without being seen" section of my attached pivot table?

       

      Basically how do I suppress the Nulls

       

      I only want to display "Total Attends" in the "Left without being seen" section

       

       

       

      Kind Regards

      Helen

        • Re: How Do I supress Expression in a pivot table
          whiteline _

          Hi.

           

          I think, you can't do it that way.

           

          I suggest to transform your column with expressions so thet it will be a dimension.

          Create your table structure instead of using ValueList().

          Then use just appropriate complex expression.

            • Re: How Do I supress Expression in a pivot table
              Helen Pippard

              Hello

               

              I know this is a big ask, but I have been trying to do this in my script, but resorted to ValueLists instead

               

              With the example I have provided, are you able to provide me with more assistance on this?

               

              Even if a little bit of help is all you can do

               

              Thanks

              Helen

                • Re: How Do I supress Expression in a pivot table
                  whiteline _

                  Hi.

                   

                  There is plenty of ways to do it. I used the easiest to implement, just to show the idea.

                   

                  Add this load to your script (a table structure and sort order):

                   

                  LOAD * INLINE [

                  Level1, Level2, Expression,

                  Total Treatment Time, Admitted, Median

                  Total Treatment Time, Admitted, Fractile

                  Total Treatment Time, Admitted, Longest Wait

                  Total Treatment Time, Non Admitted, Median

                  Total Treatment Time, Non Admitted, Fractile

                  Total Treatment Time, Non Admitted, Longest Wait

                  To Initial Treatment,     , Median

                  To Initial Treatment,     , Fractile

                  To Initial Treatment,     , Longest Wait

                  Left Without Being Seen, , Total Attends

                  ];

                   

                  Then create a pivot table whith dimensions: Level1, Level2, Expression, [Reportable Month].

                  Add this expression (I just trunsformed yours):

                  =Pick(Match(Expression, 'Median', 'Fractile', 'Longest Wait', 'Total Attends')+1, 'NA', Time(Median([Total Wait])/1440, 'hh:mm'), Time(Fractile([Total Wait],0.95)/1440, 'hh:mm'), Time(Max([Total Wait])/1440, 'hh:mm'), Count([Total Wait]))

                   

                  There is a match() part that calculates the appropriate index by 'Expression' dimension and pick() part that picks the cooresponding expression.

                  As there is only one expression, the text formats should be handled explicitly, thats why I added Time() function.