Rank() Function

[#1]

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.

[tresesco]

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) , ',')

)

[#3]

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

[tresesco]

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

[#5]

Thank you tresesco,

Please see the file attached.

[tresesco]

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

[#7]

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

See under expressions.

[tresesco]

This?

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

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

)

[#9]

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 .

[#10]

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