Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
shnqqs
Contributor II
Contributor II

Set analysis to get lowest sales amount for top sales person

Hi, I need to get lowest sales amount for top sales person.

Below is my expression: FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}>}LineSalesAmount),SalesPerson,ProductName,OrderID))

However, I will only be able to get the list of lowest sales amount for each of sales person through table.

While if I implement that expression using KPI it will give the lowest sales amount among all sales person..as this is not i wanted.. I've tried to do nested aggr and firstsortedvalue but nothing is working.

I've attached screenshot for the issue for your reference. You will see in the table list its showing the correct lowest sales for each sales person but at the above total row is showing the lowest sales among all sales person.

Thanks in advance for your help!

1 Solution

Accepted Solutions
MayilVahanan

Hi @shnqqs 

Try like below. 

If(Rowno()=0 or isnull(Rowno()), FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}, SalesPerson={"$(=FirstSortedValue(SalesPerson, Aggr(Sum({<Year={1997}>}LineSalesAmount),ProductName)))"} >}LineSalesAmount),SalesPerson,ProductName,OrderID)),

FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}>}LineSalesAmount),SalesPerson,ProductName,OrderID)))

If its not works, please provide the sample file

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

8 Replies
MayilVahanan

HI @shnqqs 

For Total column , generally, it will display the values based on the entire chart.

In order to resolve this, you can use different expression for total column like 

If(Rowno()=0 or isnull(Rowno()), FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}, SalesPerson={"YourTopSalesPersonlogictobringthevalue"} >}LineSalesAmount),SalesPerson,ProductName,OrderID)),

FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}>}LineSalesAmount),SalesPerson,ProductName,OrderID)))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
shnqqs
Contributor II
Contributor II
Author

Hi Mayil,

Big thanks to you for helping me really appreciate it!

However, I've tried using you expression its still giving the lowest sales among all of the sales person..while my concern is to find the lowest sales for top sales person.

Anyways, could you help me to understand the function for 'SalesPerson={"YourTopSalesPersonlogictobringthevalue"}  ' that you used in the expression? 

 

Thanks again and in advance for your help Mahil!

MayilVahanan

HI @shnqqs 

SalesPerson={"YourTopSalesPersonlogictobringthevalue"}  

In the place of "YourTopSalesPersonlogictobringthevalue", you need to replace the logic of "Top Sales Employee" expression.. so it will occur like  SalesPerson = {'Jamet Leverling'}

MayilVahanan_0-1622716259724.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
shnqqs
Contributor II
Contributor II
Author

Hi Mahil,

I've replaced it with the logic, however is still giving the lowest sales of all sales person. Below is the amended expression.

 

If(Rowno()=0 or isnull(Rowno()), FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}, SalesPerson={"FirstSortedValue(SalesPerson, Aggr(Sum({<Year={1997}>}LineSalesAmount),ProductName))"} >}LineSalesAmount),SalesPerson,ProductName,OrderID)),

FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}>}LineSalesAmount),SalesPerson,ProductName,OrderID)))

//FirstSortedValue(SalesPerson, Aggr(Sum({<Year={1997}>}LineSalesAmount),ProductName))

 

Thanks!

MayilVahanan

Hi @shnqqs 

Try like below. 

If(Rowno()=0 or isnull(Rowno()), FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}, SalesPerson={"$(=FirstSortedValue(SalesPerson, Aggr(Sum({<Year={1997}>}LineSalesAmount),ProductName)))"} >}LineSalesAmount),SalesPerson,ProductName,OrderID)),

FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}>}LineSalesAmount),SalesPerson,ProductName,OrderID)))

If its not works, please provide the sample file

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
shnqqs
Contributor II
Contributor II
Author

Thanks Mayil, you are a start! It works!

Just one last doubt..would  you eleborate what 'Rowno()=0 or isnull(Rowno())' is doing in the function? what is the logic being used using that?

 Thanks!

 

MayilVahanan

Hi @shnqqs 

Total will occur in Zeroth Row. So, we are replacing the total value with new expression based on our requirement.

In Pivot table, row no is null for total.

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
shnqqs
Contributor II
Contributor II
Author

Hi Mayil,

Totally understood now! Thanks for helping!!!