topic Re: Top N Analysis issue in QlikView

Top N Analysis issue

kkkumar82 — Sat, 19 Mar 2016 15:53:13 GMT

All,

Attaching a qvw file where I want to have two things

1. One in a text box

2. Another in a pivot table.

Ist requirement:

Want to see top 5 salesman along with their sales

I was able to get top 5 Salesman but not their sales

2nd requirement

Have a pivot table ProductID, SalesmanID

In the pivot table I am showing top 5 SalesmanID for each productID(got this)

also want to show the top Performers in a productID along with his sales in the pivot table

Kiran Kumar

Re: Top N Analysis issue

Chanty4u — Sat, 19 Mar 2016 15:57:59 GMT

hi,

Top n results in pivot table

display top n in pivot table

Display Top n by Dimension in Pivot table

Re: Top N Analysis issue

jagan — Sat, 19 Mar 2016 16:45:48 GMT

Hi,

Try like this using variable

Regards,

jagan.

Re: Top N Analysis issue

kkkumar82 — Sat, 19 Mar 2016 17:20:10 GMT

Hi Jagan,

Sorry I didn't find the solutions , did I miss anything.

Attaching some pics for further clarity.

I was able to get the above text box but was not able to get the Sales of the persons after them for a selected product.

In the above for Top Performer I want Charles ... name becuase he is the top sales and in the next column the sales of Charles..

Hope I am clear this time.

Thanks

Kiran Kumar

Re: Top N Analysis issue

sunny_talwar — Sat, 19 Mar 2016 21:53:10 GMT

1) Expression for 1st requirement

=If(GetSelectedCount([Product ID]) = 0, 'Select a ProductID', Concat(Distinct If(Aggr(Rank(Sum(Sales)), [Product ID],[Salesman ID]) <=5, [Salesman ID] & '-' & Aggr(Sum(Sales), [Product ID], [Salesman ID])), Chr(13)))

Re: Top N Analysis issue

sunny_talwar — Sat, 19 Mar 2016 21:58:22 GMT

2) Two expression for your 2nd requirement

Top Performer - > FirstSortedValue(TOTAL <[Product ID]> Salesman, -Aggr(Sum(Sales),[Product ID],[Salesman ID]))

Top Performer's Sales -> Max(TOTAL <[Product ID]> Aggr(Sum(Sales), [Product ID], [Salesman ID]))

Re: Top N Analysis issue

kkkumar82 — Sun, 20 Mar 2016 03:01:15 GMT

Thanks sunny it solved my problem just like that. regards Kiran Kumar