topic Re: top 3 values and corresponding attributes in QlikView

top 3 values and corresponding attributes

Wed, 30 Jul 2014 21:53:38 GMT

Product	Source	Destination	Cost	Cost 2	Total Cost
Handbag	A	A1	0	5	5
Handbag2	B	B1	10	10	20
Handbag3	C	C1	20	15	35
Handbag4	D	D1	30	10	40
Handbag5	E	E1	40	20	60

Right now, I'm using the above example where I'm using =FirstSortedValue(Source, [Total Cost],1)

Is there any way that if my data had 0 as one of the cost, not to include it as the cheapest source?

For example, the cheapest source would be B, C, and D.

Re: top 3 values and corresponding attributes

Anonymous — Wed, 30 Jul 2014 22:57:09 GMT

=IF(FirstSortedValue(Source, [Cost],1) = 0 or FirstSortedValue(Source, [Cost2],1) = 0, FirstSortedValue(Source, [Total Cost],2), FirstSortedValue(Source, [Total Cost],1))

Re: top 3 values and corresponding attributes

Thu, 31 Jul 2014 14:08:12 GMT

This doesn't seem to work properly... it gives me the same value as the =FirstSortedValue(Source, [Total Cost],1)) even though the cost2 is 0. Any other ideas?

Re: top 3 values and corresponding attributes

Anonymous — Thu, 31 Jul 2014 17:27:02 GMT

IF(FirstSortedValue(aggr(Sum(Cost),Source),Cost) = 0 or FirstSortedValue(aggr(Sum([Cost 2]),Source),[Cost 2]) = 0,

FirstSortedValue(aggr(Sum([Total Cost]),Source),[Total Cost],2),FirstSortedValue(aggr(Sum([Total Cost]),Source),[Total Cost]))

Re: top 3 values and corresponding attributes

Thu, 31 Jul 2014 18:53:12 GMT

This gives me the value. What do I do to get the field associated with that value? For example, instead of 20 in your example. I should see Handbag2.

Re: Re: top 3 values and corresponding attributes

Anonymous — Thu, 31 Jul 2014 22:06:14 GMT

IF(FirstSortedValue(Cost,aggr(Sum(Cost),Source)) = 0 or FirstSortedValue([Cost 2],aggr(Sum([Cost 2]),Source),[Cost 2]) = 0,

FirstSortedValue(Product,aggr(Sum([Total Cost]),Product),2),FirstSortedValue(Product,aggr(Sum([Total Cost]),Product)))

Re: top 3 values and corresponding attributes

simenkg — Thu, 31 Jul 2014 22:27:01 GMT

Firstsortedvalue takes set analysis.

How about:

=FirstSortedValue({$<Cost={">0"}>}Source, [Total Cost],1)

Regards
SKG

Re: top 3 values and corresponding attributes

Anonymous — Fri, 01 Aug 2014 03:14:05 GMT

Based on Simen's suggestion this is more straightforward.

=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost])

Re: top 3 values and corresponding attributes

Fri, 01 Aug 2014 20:29:48 GMT

=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost],1) seems to do the trick.

Now how do I find the Cost and Cost 2 associated with that? What is the right calculation to get the Cost and Cost 2?

From Calculation above:

cheapest:

Product, Source, Destination, Cost, Cost 2, Total Cost

Handbag2, B1, 10, 10, 20

Re: top 3 values and corresponding attributes

Anonymous — Fri, 01 Aug 2014 20:39:21 GMT

=FirstSortedValue({<Cost = {">0"},[Cost 2] = {">0"}>}Product, [Total Cost])

Replace Product in bold with what field you want to grab. You Don't need the 1 at the end because it is implied if left out.

Re: top 3 values and corresponding attributes

Fri, 01 Aug 2014 20:44:51 GMT

Thanks for your help!!