topic How to get top 5 ranking diemensions in string format in Integration, Extension & APIs

How to get top 5 ranking diemensions in string format

Wed, 25 May 2016 13:33:14 GMT

Channel	Sales	Brand	Dept
Channel1	1235	Samsung	Phone
Channel2	354	Blackberry	PDA
Channel3	941	Oppo	Phone
Channel4	341	Nokia	Phone
Channel5	924	Sansui	Phone
Channel6	1733	Toshiba	TV
Channel7	1460	Apple	Phone
Channel8	1177	Micromax	TV
Channel9	1719	Le10	Phone
Channel10	917	Maxima	Watch
Channel3	1585	Apple	Phone
Channel4	1828	Micromax	Tablet
Channel5	689	Le10	Phone
Channel6	1364	Sansui	Phone
Channel7	1774	Toshiba	TV
Channel8	232	Apple	Phone
Channel9	1093	Micromax	Phone
Channel10	1677	Le10	Phone
Channel1	375	Maxima	Phone
Channel2	1579	Le10	Phone

Hi I want to have the saleswise top 5 channels in text or string format not in number format .

I have tried

for first ranked channel - Aggr(if(Rank(Sum(Sales))=1,Channel),Channel)

similarly for second , third , fourth and fifth i have used 2,3,4,5 but this is returning me in number format.

Actually I need to feed this as an input to a set analysis where condition so it is important that this is in text or string format. Please provide any pointers.

Re: How to get top 5 ranking diemensions in string format

swuehl — Wed, 25 May 2016 19:46:45 GMT

If you actually want to use the Channel Top5 in a set expression, I would suggest an advanced search like

=Sum({<Channel = {"=Rank(Sum(Sales),4)<=5"}>} Sales)

Re: How to get top 5 ranking diemensions in string format

sunny_talwar — Wed, 25 May 2016 20:54:04 GMT

May be the OP is looking for the concatenation of all the Channels:

=Concat(DISTINCT {<Channel = {"=Rank(Sum(Sales),4)<=5"}>} Channel, Chr(10))

=Concat(DISTINCT {<Channel = {"=Rank(Sum(Sales),4)<=5"}>} Channel, ', ')

Re: How to get top 5 ranking diemensions in string format

swuehl — Wed, 25 May 2016 20:55:59 GMT

Maybe, my main point was to use the advanced search in the set expression, the aggregation function is just a sample.

Re: How to get top 5 ranking diemensions in string format

sunny_talwar — Wed, 25 May 2016 22:20:09 GMT

Got it, apologize for not understanding the intent

Re: How to get top 5 ranking diemensions in string format

swuehl — Wed, 25 May 2016 22:23:52 GMT

No worries, Sunny

Re: How to get top 5 ranking diemensions in string format

Thu, 26 May 2016 07:16:53 GMT

Hi Sunny,

Its giving invalid diemension. Because the sum (sales) inside the " " is taking up as a string . it is not detecting sales as a field. Is the expression fine or do I need to make any changes ?

Re: How to get top 5 ranking diemensions in string format

swuehl — Thu, 26 May 2016 08:48:19 GMT

Could you show a screen shot of your current expression or even better, could you upload a small sample QVF?

Re: How to get top 5 ranking diemensions in string format

Thu, 26 May 2016 08:57:46 GMT

Re: How to get top 5 ranking diemensions in string format

Thu, 26 May 2016 08:59:37 GMT

I need permission to share the qvf , its confidential basically. The data I shared was dummy data actually, sales is weeklysales in my db and channel is [DCOE_HIER.LOCATION.CHANNEL_DESC]

Re: How to get top 5 ranking diemensions in string format

swuehl — Thu, 26 May 2016 09:15:25 GMT

And the expression editor shows an explicite error message?

The advanced search will not be evaluated and interpreted by the syntax checker, so I think you shouldn't be worried by the coloring alone.