0 Replies Latest reply: Aug 22, 2016 7:42 AM by Samir Bihari RSS

    Displaying 2 Hierarchies (each 3 levels in 1 row)

    Samir Bihari

      Hi Experts ,

       

      I am new to qlikview and apologies in advance If I am asking something silly .

      I am trying to load data from MS analysis services Cube where I have 2 Hierarchies namely Region and Year (each 3 levels) .

      I have used the following MDX query after connecting to analysis services :

       

      SELECT

      {

        [Measures].[Qty],

        [Measures].[Turnover USD]

      }

      DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

      CrossJoin(

        {[Area posted].[MG Area posted].Members},

        {[Company].[Company].Children},

          {[Customer].[Customer label].Children},

        {[Product].[Product brand].Children},

        {[Date].[Calendar Y-Q-M-D].Members})

       

      ON ROWS

      FROM [Sales]

       

      But it provides the below output :

      Eaxmple :

       

      RegionSub RegionAreaYearQuarterMonthCompanyCustomer   LabelSales
      AMERN.AmericaAlbania2016TCH111-MANU10
      AMERN.AmericaAlbania2016Q1TCH111-MANU10
      AMERN.AmericaAlbania2016Q1JanTCH111-MANU10
      AMER2016Q1JanTCH111-MANU10
      AMERN.America2016Q1JanTCH111-MANU10
      AMERN.AmericaAlbania2016Q1JanTCH111-MANU

      10

      But I want the output as below :

       

       

      RegionSub RegionAreaYearQuarterMonthCompanyCustomer LabelSales
      AMERN.AmericaAlbania2016Q1JanTCH111-MANU

      10

       

      Means I don't want any duplicates and all levels in flat 1 row . Attached is the qvw file .

       

      Any pointers how I can achieve that ?

       

      Thanks

      Samir