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 :

Region	Sub Region	Area	Year	Quarter	Month	Company	Customer   Label	Sales
AMER	N.America	Albania	2016			TCH	111-MANU	10
AMER	N.America	Albania	2016	Q1		TCH	111-MANU	10
AMER	N.America	Albania	2016	Q1	Jan	TCH	111-MANU	10
AMER			2016	Q1	Jan	TCH	111-MANU	10
AMER	N.America		2016	Q1	Jan	TCH	111-MANU	10
AMER	N.America	Albania	2016	Q1	Jan	TCH	111-MANU	10

But I want the output as below :

Region	Sub Region	Area	Year	Quarter	Month	Company	Customer Label	Sales
AMER	N.America	Albania	2016	Q1	Jan	TCH	111-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