Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Rank() Function

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.

Tags (1)
13 Replies
MVP
MVP

Re: Rank() Function

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

)

Not applicable

Re: Rank() Function

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

MVP
MVP

Re: Rank() Function

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

Not applicable

Re: Re: Rank() Function

Thank you tresesco,

Please see the file attached.

MVP
MVP

Re: Rank() Function

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

Not applicable

Re: Rank() Function

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

See under expressions.

MVP
MVP

Re: Rank() Function

This?

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

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

)

Not applicable

Re: Rank() Function

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 .

Not applicable

Re: Rank() Function

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

Community Browser