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

[jagan]

Hi Manish,

Use this for Average Weight:

Avg({<Supplier=>}Aggr(
		IF(
			(SUM({<Supplier=>}Sales)/SUM({<Supplier=>}Cost)-1)>=From and (SUM({<Supplier=>}Sales)/SUM({<Supplier=>}Cost)-1)<= To
		,Rank) * Only ({<Supplier=>}Weight)
	,Rank,PartYearKey,Supplier))

/SUM({<Supplier=>}Total Weight)

Will update once got the Rank expression.

Regards,

Jagan.

[CELAMBARASAN]

Hi,

As Jagan suggested, you have to add the Supplier exclusion in all your aggregation functions. It will work.

Thanks,

Celambarasan

[MK_QSL]

Its not working for Rank...

[MarcoWedel]

Hi Manish,

probably not the most straight forward solution, but I guess it works ...

... sort of. 

hope this helps

regards

Marco

[Gysbert_Wassenaar]

I really like Marco‌'s solution. See attached qvw for another try.

[MK_QSL]

Hi Gysbert,

Thanks for your reply...

But Rank is not coming up...

[MK_QSL]

Rank is missing here

[Oleg_Troyansky]

I think I answered a similar question about ranking in this blog post:

Q-Tip #8 – Ranking the Unrankable | Natural Synergies

cheers,

Oleg Troyansky

[MK_QSL]

Hi Oleg,

Thanks for your reply. I already used your suggested method but here the Rank is based on data from more than one table and there is an aggregation also. Which is not giving me the desired result.