1 Reply Latest reply: Jun 10, 2011 6:21 AM by Olaf Gschweng RSS

    Dynamic pivot table columns (formula) by Listbox selection

      Hi

       

      I tried to enable and disable columns of pivot table by selecting it with a Listbox.

       

      My approach was:

       

      1: I created a Inline Table with the selection of columns I want to use in the Pivot Table.(the columns are calculations saved as a variable)

       

      ie.:

       

       

      LOAD

      * INLINE [
      Spaltenauswahl
      Gewinn
      Gewinn Vorjahr
      Gewinnentwicklung
      Umsatz
      Umsatz Vorjahr
      Umsatzentwicklung
      Kosten
      Kosten Vorjahr
      Kostenentwicklung
      ]

      ;

       

      2:  The formula for each column of the Pivot Table is defined in the following way. (concat is needed for selecting more than 1 column for the pivot table)

       

      if(

       

       

      WildMatch( Concat( WildMatch(Spaltenauswahl, 'Gewinn','Gewinn Vorjahr','Gewinnentwicklung','Kosten','Kosten Vorjahr','Kostenentwicklung', 'Umsatz', 'Umsatz Vorjahr','Umsatzentwicklung'),',' ),'*1,*') = 1
      ,
      $(Gewinn)

       

       

       

      Now I dont know how to disable a not selected column by the help of this formula.

       

      Perhaps there is an easier way to connect the selected columns of the Listbox with the pivot table formula.

      Hope somebody can help me

       

       

        • Re: Dynamic pivot table columns (formula) by Listbox selection

          Found the solution for table diagrams

           

          There I had to add

           

          if(

          wildmatch(concat(wildmatch(Spaltenauswahl, 'Gewinn','Gewinn Vorjahr','Gewinnentwicklung','Kosten','Kosten Vorjahr','Kostenentwicklung', 'Umsatz', 'Umsatz Vorjahr','Umsatzentwicklung'),',' ),'*1*') = 0 , 0)

           

           

          under "properties -> presentation

          to the condition of the column "Gewinn"

           

          But its not working for pivot tables

          Anyone got an idea for pivot tables?