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)



          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 !!!