topic Re: Get the name with the highest count units and least sum price in App Development

Get the name with the highest count units and least sum price

Itay113 — Thu, 07 Mar 2024 23:25:55 GMT

I have a table with these 3 columns:

Name, Units, Price

I want to display 2 kpi's:

First, I want to display the name of the product with the highest count of units and least sum of prices

Secondly, I want to to display the name of the product with highest number units and least number of price,

I tried this set analysis but this didn't display anything unless I remove of the conditions:

`{<Units = {"$(=max(Units))"}, Price = {"$(=min(Price))"}>} Name`

Re: Get the name with the highest count units and least sum price

Anil_Babu_Samineni — Fri, 08 Mar 2024 00:04:32 GMT

@Itay113 Perhaps this way?

#1, firstSortedValue(Name, -Min({<Name={"=Max(Aggr(Count(Units), Name))"}>} Price)

#2, firstSortedValue(Name, -Max({<Name={"=Min(Price)"}>} Units)

Re: Get the name with the highest count units and least sum price

Itay113 — Fri, 08 Mar 2024 09:54:35 GMT

I get that I missing `)` or `,` in both of them, I tried to add `)` at the end but then I get that nested aggregation not allowed

Re: Get the name with the highest count units and least sum price

Anil_Babu_Samineni — Sat, 09 Mar 2024 15:08:07 GMT

@Itay113 Do you have sample data set that I can look? Please feel free to add the outcome as well.

Re: Get the name with the highest count units and least sum price

Itay113 — Sat, 09 Mar 2024 15:11:44 GMT

@Anil_Babu_Samineni

Re: Get the name with the highest count units and least sum price

Itay113 — Sat, 09 Mar 2024 15:12:32 GMT

@Anil_Babu_Samineni in this sample data I refer to UnitsOnOrder field

Re: Get the name with the highest count units and least sum price

Anil_Babu_Samineni — Sat, 09 Mar 2024 15:14:22 GMT

@Itay113 Can you please attach as excel file or QVF that you have to see, And from that, you have 2 requests, please provide the output what you want to see.

Re: Get the name with the highest count units and least sum price

Itay113 — Sat, 09 Mar 2024 15:24:35 GMT

@Anil_Babu_Samineni this app has sample fake data of what I am actually working on, in my real data each name may occur couple of times so I want to display 1 kpi that shows a single name that has the highest count of UnitsOnOrder and the least sum of PricePerUnit, 2 kpi that shows a single name that has the highest UnitsOnOrder and the least PricePerUnit

Re: Get the name with the highest count units and least sum price

Anil_Babu_Samineni — Sat, 09 Mar 2024 15:47:12 GMT

@Itay113 Thanks for the file,

KPI#1, that has the highest count of UnitsOnOrder and the least sum of PricePerUnit

For this, It means UnitsOnOrder is 39 and associated to PricePerUnit is 18, So the outcome should be "Chai"?

KPI#2, that has the highest UnitsOnOrder and the least PricePerUnit

The difference from KPI#1 to KPI#2 is that there is no Aggregate functions (Like, Count and Sum)? This case, how to calculate the output?