2 Replies Latest reply: Nov 14, 2017 10:27 AM by Diogo Carvalheira RSS

    [QlikSense & Mashup] Implementing Hypercubes with Two-level Dimensions

    Diogo Carvalheira

      Hello,

       

      I've been struggling with this subject for a while.

       

      I have two Dimensions like "Country" and "City" (city belongs to Country) with two measures "Sum(Null_Votes)" and "Sum(TotalVotes)" and I want to present the data in a mashup with a pivot table and fully expanded with partial and total sums.

      At this pages:

      http://help.qlik.com/en-US/sense-developer/3.0/Subsystems/Extensions/Content/Overview/qListObjectDef.htm

      GetHyperCubePivotData method ‒ Qlik Sense

      HyperCubeDef ‒ Qlik Sense

       

      I found that it's possible to group elements like "NxInlineDimensionDef" with the property "qGrouping" = "H" for drill-down, but i can't understand how I place the Dimensions in the code, or what properties I have to use for reading the data in the javascript. I only achieved passing the 1st dimension values (Country).

      Here goes my code:

       

             me.createTable=function(){

                        app.obj.app.createCube({

                  "qDimensions": [{

                      "qDef": {

                          "qFieldDefs": ["Country"],

                          "qGrouping":"H"

                      },

                      "qNullSuppression": true,                  

                      "qOtherTotalSpec": {

                          "qOtherMode": "OTHER_OFF",

                          "qSuppressOther": true,

                          "qOtherSortMode": "OTHER_SORT_DESCENDING",

                          "qOtherCounted": {

                              "qv": "5"

                          },

                          "qOtherLimitMode": "OTHER_GE_LIMIT"

                      }

                  },

                  {

                      "qDef": {

                          "qFieldDefs": ["City"],

                          "qGrouping":"H"

                      },

                      "qNullSuppression": true,

                      "qOtherTotalSpec": {

                          "qOtherMode": "OTHER_OFF",

                          "qSuppressOther": true,

                          "qOtherSortMode": "OTHER_SORT_DESCENDING",

                          "qOtherCounted": {

                              "qv": "5"

                          },

                          "qOtherLimitMode": "OTHER_GE_LIMIT"

                      }

                  }],

                  "qMeasures": [

                      {

                          "qLabel": "Null_Votes",

                          "qLibraryId": "FeppgM",

                          "qSortBy": {

                              "qSortByState": 0,

                              "qSortByFrequency": 0,

                              "qSortByNumeric": 0,

                              "qSortByAscii": 1,

                              "qSortByLoadOrder": 0,

                              "qSortByExpression": 0,

                              "qExpression": {

                                  "qv": " "

                              }

                          }

                      },

                      {

                          "qLabel": "TotalVotes",

                          "qLibraryId": "RvbWUtb",

                          "qSortBy": {

                              "qSortByState": 0,

                              "qSortByFrequency": 0,

                              "qSortByNumeric": 0,

                              "qSortByAscii": 1,

                              "qSortByLoadOrder": 0,

                              "qSortByExpression": 0,

                              "qExpression": {

                                  "qv": " "

                              }

                          }

                      }

                  ],

                  "qInitialDataFetch": [

                      {

                          "qTop" : 0,

                          "qLeft" : 0,

                          "qHeight": 200,

                          "qWidth": 5

                      }

                  ],

                  //"qAlwaysFullyExpanded":true,

                  //"qShowTotalsAbove":true,

                  "qSuppressZero": true,

                  "qSuppressMissing": true,

                  "qMode": "P",

                  "qInterColumnSortOrder": [],

                  "qStateName": "$"

                  },function(reply) {

                     

                  console.log(reply);

                  var country, city, null_Votes,totalVotes;

                  var Table=[];

       

                  var Data1 =reply.qHyperCube.qPivotDataPages[0].qLeft;

                  var Data2 =reply.qHyperCube.qPivotDataPages[0].qData;

                 

                  for (var i=0; i<Data1.length; i++) {

                      country= Data1[i].qText;

                      //city= Data[i][1].qText;

                      Null_Votes= Data2[i][0].qNum;

                      TotalVotes = Data2[i][1].qNum;

                     

                      Table.push({

                          Country:country,

                          //City:city,

                          Null_Votes:null_Votes,

                          TotalVotes:totalVotes,

                          }); 

                  }

                  $scope.Table = Table;

              });

              }

       

      The result I'm getting:

      Screenshot_2.png

      I want to, between each country, have the respective cities with the their values.

       

      Thanks a lot for your feedbacks, ideas, suggestions, advices.