Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
canerkan
New Contributor III

Sorting every Dimension in a Pivot Table

Hi everyone!

I am trying to sort every Dimension in my pivot table. I found a good solution for my problem in this Discussion:

https://community.qlik.com/t5/New-to-Qlik-Sense/Pivot-Table-Sorting-Qlik-sense/m-p/1417387

Solution of this Discussion:

Formula Dimension 2:

=AGGR(DUAL(Group,RowNo(TOTAL)),Shop,Group)

Formula Dimension 3:

=AGGR(DUAL(Item,RowNo(TOTAL)),Shop,Group,Item)

This makes each Value unique in my subgroups.

But if I change the Levels of my Dimensions, the values become duplicates as you can see in the picture. Usually, the dimension "Länder" (=Country) comes first, after that "Artikelnr."(=Article groups) then the order you can see in the picture.

This is the order I wrote in the formula of each Dimension either. But to get different impressions of the Data, the user will change the order of them.  Which makes it impossible to sort the Values in this way. Does anyone have an idea how to make it more flexible?

 

Thank you very much in advance!

Can

2 Replies
thiago_justen
Valued Contributor III

Re: Sorting every Dimension in a Pivot Table

I'm not sure if it will solve your problem but I suggest you just put this expression in both of dimensions.

 

AGGR(DUAL(Item,RowNo(TOTAL)),Shop,Group)

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
canerkan
New Contributor III

Re: Sorting every Dimension in a Pivot Table

Hi Thiago_justen,

thanks for your answer. These formulas were just an example.. Would you mind giving me a suggestions on these?

1. Dimension: =Länder  (=Country)

2. Dimension: =AGGR(DUAL([Artikelnr.],RowNo(TOTAL)),[Länder], [Artikelnr.])   (=Product Group)

3. Dimension: =AGGR(DUAL(Name,RowNo(TOTAL)),[Länder], [Artikelnr.], Name)  (=Customer)

4. Dimension: =AGGR(DUAL([Alte Art.-Nr.-Artikel],RowNo(TOTAL)),[Länder],[Artikelnr.],Name,[Alte Art.-Nr.-Artikel])   (=Product ID)

The Problem is, if I would put Dimension 3 (=Customer) on the first place, the customer would appear for each Product Group he purchased a Product of. I think this is because of the structure I built on the last part of my formula (underlined).