Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

top 3 values and corresponding attributes

ProductSourceDestinationCostCost 2Total Cost
HandbagAA10

5

5
Handbag2BB1101020
Handbag3CC1201535
Handbag4DD1301040
Handbag5EE1402060

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.

1 Solution

Accepted Solutions
Creator III
Creator III

Based on Simen's suggestion this is more straightforward.

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

View solution in original post

10 Replies
Creator III
Creator III

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

Not applicable

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?

Creator III
Creator III

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

Not applicable

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.

Creator III
Creator III

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

Partner
Partner

Firstsortedvalue takes set analysis.

How about:


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

Regards
SKG

Creator III
Creator III

Based on Simen's suggestion this is more straightforward.

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

View solution in original post

Not applicable

=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






Creator III
Creator III

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