topic how write Calculated dimension using if,rank and concat in QlikView

how write Calculated dimension using if,rank and concat

nazaninslp — Sat, 16 Nov 2024 19:30:06 GMT

Dears

I wrote following calculated dimension which is show my top 10 selling titles based on revenue for current year.

Dimension: =if(Aggr(rank(sum({<Year={"$(=Max(Year))"}>}Revenue)),SellingTitle)<=10,SellingTitle)

now I want to concat my selling title with Content Title in this code

Can you please help me to where I should put the CONCAT in this dimension code?

Fields: SellingTitle, ContentTitle

y_grynechko — Sat, 07 Dec 2019 12:24:11 GMT

Hey,

in the dimension itself you can use '&' symbol to put together two dimensions.

=if(Aggr(rank(sum({<Year={"$(=Max(Year))"}>}Revenue)),SellingTitle)<=10,SellingTitle & ContentTitle)

you can add ' - ' to make it look better:

=if(Aggr(rank(sum({<Year={"$(=Max(Year))"}>}Revenue)),SellingTitle)<=10,SellingTitle& ' - ' &ContentTitle)

nazaninslp — Sat, 07 Dec 2019 12:43:45 GMT

Dear y_grynechko

Thank you so much, it is working fine.

but I faced with another problem!

now my Content Title is not sorted, because I need show top 10 content title and selling title based on revenue,

every selling title may have multiple content title

can you help me how fix that?

=if(Aggr(rank(sum({<Year={"$(=Max(Year))"}>}Revenue)),SellingTitle)<=10,SellingTitle& ' - ' &ContentTitle)