topic Re: Rank should not change on selection in QlikView

Rank should not change on selection

MK_QSL — Tue, 11 Aug 2015 15:30:33 GMT

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

Rank for each Supplier wrt Part
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.

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.
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‌

Re: Rank should not change on selection

jagan — Tue, 11 Aug 2015 16:47:44 GMT

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.

Re: Rank should not change on selection

CELAMBARASAN — Tue, 11 Aug 2015 18:12:12 GMT

Hi,

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

Thanks,

Celambarasan

Re: Rank should not change on selection

MK_QSL — Tue, 11 Aug 2015 18:14:23 GMT

Its not working for Rank...

Re: Rank should not change on selection

MarcoWedel — Tue, 11 Aug 2015 23:27:37 GMT

Hi Manish,

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

... sort of.

hope this helps

regards

Marco

Re: Rank should not change on selection

Gysbert_Wassenaar — Thu, 13 Aug 2015 08:41:20 GMT

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

Re: Rank should not change on selection

MK_QSL — Thu, 13 Aug 2015 08:55:17 GMT

Hi Gysbert,

Thanks for your reply...

But Rank is not coming up...

Re: Rank should not change on selection

MK_QSL — Thu, 13 Aug 2015 16:02:11 GMT

Rank is missing here

Re: Rank should not change on selection

Oleg_Troyansky — Mon, 17 Aug 2015 14:40:27 GMT

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

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

cheers,

Oleg Troyansky

Re: Rank should not change on selection

MK_QSL — Mon, 17 Aug 2015 14:48:52 GMT

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.

Re: Rank should not change on selection

Oleg_Troyansky — Mon, 17 Aug 2015 15:14:23 GMT

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

Re: Rank should not change on selection

Gysbert_Wassenaar — Mon, 17 Aug 2015 15:25:47 GMT

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)))

Re: Rank should not change on selection

MK_QSL — Mon, 17 Aug 2015 15:27:43 GMT

Hi Oleg,

Getting Closed now... !

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

Re: Rank should not change on selection

Oleg_Troyansky — Mon, 17 Aug 2015 15:37:28 GMT

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.

topic Re: Rank should not change on selection in QlikView

Rank should not change on selection

HRLinder

mov

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection

Re: Rank should not change on selection