topic Re: Re: Rank() Function in QlikView

Rank() Function

Mon, 11 Aug 2014 08:11:48 GMT

Hi All,

It seems I could not achieve the desired result in my Table.

I have a Table of "Positive 1 STD Exceptions", Where each raw appear only if it is a "Positive 1 STD Exception".

That Table includes 3 dimensions: App, Advertiser and Campaign.

I would like to add a column as an expression labeled "Main Partner" to that table, where I will see the name of the partner which brought me the most revenue (from yesterday and belongs to "PartnerReport", as you can see in my set analysis).

My Expression is:

=if([Exception type]='Positive 1 STD Exception',

if(rank(sum({<isYesterdayDate={'1'},PartnerReport={'PartnerReport'}>} (ClickCost+InstallCost)/100)) <2, partner_name)

)

***Exception Type Should NOT be part of the set analysis, please do not answer with this as your answer...

I seem to be getting Null only as my results....

Can you Help?

Kind Regards,

Ella.

Re: Rank() Function

tresB — Mon, 11 Aug 2014 08:35:46 GMT

If the possible count of partner_name is more than one, concat() should be used like:

=if([Exception type]='Positive 1 STD Exception',

Concat(if(rank(sum({<isYesterdayDate={'1'},PartnerReport={'PartnerReport'}>} (ClickCost+InstallCost)/100)) <2, partner_name) , ',')

)

Re: Rank() Function

Mon, 11 Aug 2014 09:01:20 GMT

Thank you but this is not my issue.

I think I have a clue for the root of my problem:

I do not have the "Partner name" in my dimensions, however, I want the ranking to be according to each partner.

So I think I need to add Aggr() somehow.

If you can assist that would be great,

I will keep trying.

Thank you,

Ella

Re: Rank() Function

tresB — Mon, 11 Aug 2014 09:06:42 GMT

Easiest way to get help is attaching a sample qvw and explaining the required output against the sample data there.

Re: Re: Rank() Function

Mon, 11 Aug 2014 09:20:06 GMT

Thank you tresesco,

Please see the file attached.

Re: Rank() Function

tresB — Mon, 11 Aug 2014 09:26:32 GMT

I don't find a field called - '[Exception type]'.

Re: Rank() Function

Mon, 11 Aug 2014 09:36:21 GMT

It is there, it's just hidden (in presentation).

See under expressions.

Re: Rank() Function

tresB — Mon, 11 Aug 2014 09:48:58 GMT

This?

=if([Exception type]='Positive 1 STD Exception',

If(rank(sum({<isYesterdayDate={'1'},PartnerReport={'PartnerReport'}>} (ClickCost+InstallCost)/100))<2, Concat(partner_name,','))

)

Re: Rank() Function

Mon, 11 Aug 2014 10:26:10 GMT

Hi Elula ,

if([Exception type]='Positive 1 STD Exception',
only({<partner_id={"=aggr(rank(sum({<isYesterdayDate={'1'},PartnerReport={'PartnerReport'}>} (ClickCost+InstallCost)/100)), partner_name)<=1"}>}partner_name)
)

This will work .

All The Best . Happy Learning .

Re: Rank() Function

Mon, 11 Aug 2014 10:30:39 GMT

Can you use below expression in Main Partner expression:

if([Exception type]='Positive 1 STD Exception' and rank(sum({<isYesterdayDate={'1'},PartnerReport={'PartnerReport'}>} (ClickCost+InstallCost)/100)) <2,partner_name)

Hope this was as expected

Thanks.

Singh

Re: Rank() Function

Mon, 11 Aug 2014 11:37:26 GMT

It works alright in the QVW I put because there is only 1 partner there, but when I move it to my original QVW it just returns all partners with concat...

Re: Rank() Function

Mon, 11 Aug 2014 11:51:07 GMT

This works in the QVW I uploaded, where there is only 1 partner,

however, when I use it in my original QVW (with many partners) it only returns the answer in the raw where the suitable partner was the partner that gave the most profit from all others.

In any other raw it does not returns an answer, but shows "-".

Re: Rank() Function

Mon, 11 Aug 2014 12:15:47 GMT

Hi Elula ,

Now i cleared with your actual requirement , that you want to list all the partner name right .

Use following expression , hope it will works

if([Exception type]='Positive 1 STD Exception',
concat(if(aggr(rank(sum({<isYesterdayDate={'1'},PartnerReport={'PartnerReport'}>} (ClickCost+InstallCost)/100)), partner_name)>0,partner_name),','))

All the best . Happy Learning .

Re: Rank() Function

Mon, 11 Aug 2014 12:26:47 GMT

I see I need to upload a new QVW with more data...