Ranking of Locations and Groups in grouped Dimension | Ranking von Standorten und Warengruppen in gruppierter Dimension

[Anonymous]

English:

Hello community,

I am currently trying to build a dashboard in Qlikview where I can provide several KPIs like PlannedSalesVolume, ActualSalesVolume, SalesIndex and much more per Location and ClassOfGoods.

Therefore I made a Drilldown Dimensiongroup which includes Location and ClassOfGoods.

Now I want to do a Ranking of the Locations and ClassOfGoods.

I can already rank the Locations after SalesIndex (Actual/PlannedSalesVolume) with the Rank()-function, but I want that if someone goes one Layer deeper (from Location to ClassOfGoods), that the rank is not calculated for the ClassOfGoods inside the selected Location, but the rank of the selected Location inside the Classes of Goods.

For Example:

First Dimension of Group (Location):

Location	SalesIndex	Rank
1	90	2
2	100	1
3	70	3

Second Dimension of Group (ClassOfGoods), Location 1 is selected:

ClassOfGoods	SalesIndex	Rank
Cars	80	50
T-Shirts	120	2
Tools	90	30

Does anyone have an idea? I would be really grateful!

Best Regards

Flo

German:

Hallo zusammen,

ich versuche aktuell ein Dashboard in Qlikview aufzubauen, das KPIs wie Planumsätze, Istumsätze, Umsatzindex uvm. pro Standort und Warengruppe darstellen kann.

Zu diesem Zweck habe ich eine Drilldown-Dimensionsgruppe erstellt in der Standorte und Warengruppen enthalten sind.

Dazu möchte ich ein Ranking der Standorte und Warengruppen pro Standort erstellen.

Ich kann auch die Standorte nach Umsatzindex (Ist/Planumsatz) ranken (RANK(...)), allerdings möchte ich, wenn man sich eine "Ebene tiefer klickt" dann nicht den Rang der Warengruppen innerhalb des Standorts erhalten, sondern den Rang des selektierten Standorts in der jeweiligen Warengruppe.

Also  z.B. Übergeordnete Dimension:

Standort	UmsatzIndex	Rank
1	90	2
2	100	1
3	70	3

Untergeordnete Dimension (Standort 1 selektiert):

Warengruppe	UmsatzIndex	Rank der selektierten Filiale in der Warengruppe
Autos	80	50
T-Shirts	120	2
Werkzeuge	90	30

Hat irgendjemand eine Idee, ich wäre sehr dankbar!

Viele Grüße

Flo

[sunny_talwar]

Would you be able to share a sample (qvw file) to check the issue?

[Anonymous]

Hey Sunny,

thanks for your reply.

I can't upload it at the moment because it's pretty big and contains company data.

Maybe if I find some time soon I can build a sample file.

[sunny_talwar]

This might help:

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[sasikanth]

HI,

with Sum(SalesIndex) and Rank(Sum(SalesIndex)) chart is displaying as expected.

Load * Inline [

Location, CostOfGoods,SalesIndex

1,Cars,200

1,Tools,300

1,Machines,400

2,Cars,300

2,Tools,500

2,Machines,50

3,Cars,300

3,Tools,500

3,Machines,400

];

[Anonymous]

Hey,

I just made a very simplified sample.

Please be aware that in the original filethere are several Dimensions like Day,Month,Year etc. behind the numbers.

I guess the problem might be hard to explain and understand, so I also attached a picture of what I exactly want.

On the highest Dimension (Location) I want the Rank of the Location just like I get it with Rank(sum(...),4).

On the second (and maybe third) Dimension I want the Rank of the Location INSIDE the Module

and NOT the rank of the Module inside of the Location (like the normal Rank-function gives me).

Thanks for your help!

[sunny_talwar · Accepted solution]

May be this

=If(GetSelectedCount(STA_BESCHREIBUNG_LANG) = 0,

Rank(Sum({<STA_BESCHREIBUNG_LANG>}BEW_UMSATZ_VK)/Sum({<STA_BESCHREIBUNG_LANG>}PL_UMSATZ_VK), 4),

Aggr(Rank(Sum({<STA_BESCHREIBUNG_LANG>}BEW_UMSATZ_VK)/Sum({<STA_BESCHREIBUNG_LANG>}PL_UMSATZ_VK), 4), MODUL_BESCHREIBUNG_LANG,  STA_BESCHREIBUNG_LANG))

[Anonymous]

Hey,

for the moment it works like a charm.

Thank you really much for helping me out!