1 Reply Latest reply: Jun 21, 2017 7:17 AM by luis martin-roldan RSS

    GetObjectField is not being updated when pivoting dimensions

    luis martin-roldan

      Hi all,

       

      Summary: using GetObjectField(Dimensionality()-1) as a measure in a pivot table shows correct Field names at first, but when you then proceed to pivot some dimensions, this measure does not stay up to date with the new indexes. However, if I pivot in Edit mode sliding up and down the dimensions in the properties pane, it does work. Is this a bug or what?

       

       

      The thing is that, depending on both the expansion level and its value for each register the calculation done should vary.

       

      More specifically:

       

      IF( //lvl1

        Dimensionality()=1,

        IF(

          IsNull(DIMENSION1),

              Null(),

              Sum(TOTAL <DIMENSION1> VALUE),

          ),

          IF( //lvl2

          Dimensionality()=2,

          IF(

              IsNull(DIMENSION2),

                  Sum(TOTAL <DIMENSION1> VALUE),

                  Sum(TOTAL <DIMENSION1,DIMENSION2> VALUE),

              ),

                [...]

          ) //lvl2

      ) //lvl1

       

      That code above is the prototype of what I seek, because it relies on a specific dimensions order in the pivot table. In order to make it pivot-independent I intended to make use of the GetObjectField function (actually, GetObjectDimension would be much better but since there is no documentation whatsoever I feel reluctant about it) along with Dimensionality. Thus:

       

      IF( //lvl1

        Dimensionality()=1,

        IF(

          IsNull(GetObjectField(Dimensionality()-1)),

              Null(),

              Sum(TOTAL <GetObjectField(Dimensionality()-1)> VALUE),

          ),

          IF( //lvl2

          Dimensionality()=2,

          IF(

              IsNull(GetObjectField(Dimensionality()-1)),

                  Sum(TOTAL <GetObjectField(Dimensionality()-1)> VALUE),

                  Sum(TOTAL <GetObjectField(Dimensionality()-2),GetObjectField(Dimensionality()-1)> VALUE),

              ),

                [...]

          ) //lvl2

      ) //lvl1


      And from then on, optimize the code since this could scale to heinous lengths.


      The problem is that GetObjectField(Dimensionality()-1) is not updating when you pivot alongside the table, not out of edit mode!

       

      Message was edited by: luis martin-roldan I removed "Bug Report:" from the title as it was rather scandalous and unnecesary.

        • Re: GetObjectField is not being updated when pivoting dimensions
          luis martin-roldan

          Recieved input on the matter directly from Qlik Support (thankfully).

           

          Here's an extract of their answer:

           

           

          In order to verify what I'm saying here, you can open Chrome developer tools and get into the WebSocket frames tab, to see exactly what happens between the Client and Engine of Qlik Sense.

           

          1. When you are re-organizing the order in Pivot table out of the Edit mode, the changes you made are only "soft changes" to the layout. You can verify this in the communication in WebSocket. There are only "ApplyPatches" and "GetLayout" requests sent to and replied by engine. This will only determine the new order of the dimensions in the apparance of the object. The data structure of the object on Engine side is still the old one. That's why you don't see the change reflected in the object, because the 2 functions will calculate based on the real position of the fields in the object.

          2. Now if you open the Edit mode with the "soft change" there, you will be prompted to either Apply or Discard the change when you select the object. Select "Apply". Even at this stage it just confirms the order change in the object apparance, not the actual data structure in the object on Engine side. You will get a response like "qInterColumnSortOrder:[2, 3, 0, 1, 4, 5, 6, -1]", which the actual position the functions are using for calculation is still [0,1, 2, 3, 4, 5, 6, -1] .

          3. Now if you change the order by move them in the properties pannel, you will see that the client will send a SetProperties and GetProperties requests. Now the actual position in the object on Engine side and the layout coming after that are both changed. And this is why your calculation is working again.

           

          This is by far as design of how Pivot table works in Qlik Sense. Therefore unfortunately your original plan won't work under this design.