Rank should not change on selection

[MK_QSL]

I have three tables

Sales :   This table is having Sales information along with few more fields.

Rank :    This table is showing Rank for each part on Yearly basis. From and To is profit range

Weightage : This table contains Weightage of Parts for Yearly basis

Objective is

1. Rank for each Supplier wrt Part
2. Weighted Average

Excel file and QVW both included for your reference.

The above requirement is already achieved using aggr function (check pivot table from QVW file)

but tricky part is user wants Rank and Weighted Average should not be changed on selecting Supplier.

i.e. check Pivot Table.

Here for S4

                AverageWeight = 0.3148 and

                Rank = 3

(For partial sum of Part column)

If user select S4 from Supplier List Box, the above values (0.3148 and 3) should not change.

In my case, when I am selecting S4, the Weighted Average changes to 0.4048 and Rank to 1.

1. The example provided is having transformed data but in real application, there are many fields and because of that I can’t aggregate SUM(Sales) and SUM(Cost) at Script Level.
2. Also, I can’t use IntervalMatch between Sales and Rank Table. I can’t change script at this stage. So the solution must be at front end also.

Please help !

Bill Markham

Celambarasan Adhimulam

Clever Anjos

Gysbert Wassenaar

Jagan Mohan Rao

Jonathan Dienst

Marco Wedel

Marcus Sommer

Massimo Grossi

Oleg Troyansky

Peter Cammaert

Ralf Becher

Rob Wunderlich

Steve Dark

Yuri Nicolett

sunindia‌

tresesco‌

sunindia‌

HRLinder

jontydkpi

hic‌

mov

[Oleg_Troyansky]

Hi Manish,

the key to the "trick" is that the Rank function should reside within the AGGR() function, and that all the aggregations within the AGGR() are configured to ignore the selection of Supplier. That includes the implied aggregations such as From and To.

Since you are already using AGGR as part of your calculation, you have to add another AGGR for the purpose of correct ranking. I'm attaching your app with the corrected expression.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[Gysbert_Wassenaar]

Try:

Round(avg(aggr(Rank(Avg({<Supplier>}Aggr(IF(

  (SUM({<Supplier>}Sales)/SUM({<Supplier>}Cost)-1)>=only({<Supplier>}From)

  and (SUM({<Supplier>}Sales)/SUM({<Supplier>}Cost)-1)<= only({<Supplier>}To)

  ,only({<Supplier>}Rank)) * Only ({<Supplier>}Weight)

  ,Rank,PartYearKey,Supplier)) /SUM({<Supplier>}Total Weight),4),Part,Supplier)))

[MK_QSL]

Hi Oleg,

Getting Closed now... !

The Rank we are getting here is in Decimal.. Can we have Rank like 1,2,3,4,5 ?

[Oleg_Troyansky]

The rank is a whole number for each individual product. When you average out multiple ranks into the Suppliers' ranking, you are getting the decimal numbers. I suppose you can round them, but this is simply the logical result of averaging out multiple ranks into one.