2 Replies Latest reply: Jan 28, 2016 5:49 AM by André Kabalkin RSS

    new column in the pivot table

    André Kabalkin

      Post edited by Community Team member:

      Please post in English unless in a language specific area.

      Post translated by Google Translate (so you might need to make some minor changes).

      ---

       

      Good day,

       

      I desperately need your help in QlikView:


      I have a pivot table with two dimensions: created cities and months. In Formula I will show the hour totals. So far so good. Now I want to add a new column: budget figures. The budget figures refer only to the individual cities, not the months. At this point, I get a problem. Once I get a new formula - budget figures - create, then this will be added at the end, but behind every month column. And that's what I want to avoid. On the second screenshot you can see that.


      I have tried to represent this column as a new dimension, it did not work.

       

      How can I add back only one column / formula, without coming into the Pivot table goes in. Or you can hide these "multiple" column? Is there another alternative?

      After this column / formula I will create another one, where I ausrechne the total difference.


      Thanks in advance.

        • Re: new column in the pivot table
          Marcus Sommer

          Each expressions will be plotted for each dimension-value. This meant there aren't options to change that.

           

          One alternatively is to remove the dimension (Month) and to create each column with an own expression like in this example:

           

          sum({< Month = {"$(=max(Month) - 3)"}>} Std)

          sum({< Month = {"$(=max(Month) - 2)"}>} Std)

          sum({< Month = {"$(=max(Month) - 1)"}>} Std)

          sum({< Month = {"$(=max(Month))"}>} Std)

          sum(Planstunden)

           

          In this case you will need rather a MonthID to consider year-changes and to get an identically format - Nov with 11 won't match within the set analysis.

           

          Another possibility might be to create a caluclated dimension with your Planstunden but they will probably need be wrapped within an aggr-function like:

           

          aggr(sum(Planstunden), $(=getcurrentfield(YOURDRILLDOWNGROUP)))

           

          Have also a look here Calculated Dimensions.

           

          - Marcus

            • Re: new column in the pivot table
              André Kabalkin

              Dear Marcus,

               

              thank you for your prompt answer ! The first option works very fine, thank you !!!

              By your recommendation I created for each month a column with theses formel (sum({< Month = {"$(=max(Month) - XX)"}>} Std)). And all columns that doesn't have any value, I have hidden


              Your another recommendation doesn't work by me.


              Thank you very much !!!


              André