Need help in pivot table sort

[#1]

Hi All,

I am using the rank function in the dimension to show the Top 10 destinations by minutes. 2 dimensions i have given week and destination. expression is simple sum(Minutes).

Please look at the attached image. But it is not sorted properly. I didn't mention anything in the Sort tab. Should i have to give any expression in the Sort tab.

=if(aggr(rank(sum(Int_Mins)),zone_country_name)<=10,zone_country_name). This is the dimension

Thanks,

Raghu.

[#2 · Accepted solution]

Hi Raghu,

try to add additional calculated dimension:

=IF(
aggr(
rank(total aggr( sum(Mins),Country),0,1)
,Country)
<=10,
aggr(
rank(total aggr( sum(Mins),Country),0,1)
,Country)
)

In this case you woun't need to write anything in Sort tab.

I've attached QW application, as an example.

Hope this will help you.

Milda

[#3]

Milda,

Thank you very much. Now i am getting the correct sort order.

Thanks again

-Raghu.

[#4]

I need a small help from you guys. Look at the attached image. Here i used the below expression to show the top 5 destinations in the line graph. Its fine. In that i need to show the Total minutes too in the graph. If i add an expression like =Sum(Total Mins) then graph not showing what i expected.

IF(
aggr(
rank(total aggr( sum(Mins),Country),0,1)
,Country)
<=10,
aggr(
rank(total aggr( sum(Mins),Country),0,1)
,Country)
)

[#5]

Hi Milda,

The upload files is not working so didn't upload the sample qvw file. The below expression is fine but i want to select the country based on last date or last week. How i can add a date in the below expression to get the country.

IF(
aggr(
rank(total aggr( sum(Mins),Country),0,1)
,Country)
<=10,
aggr(
rank(total aggr( sum(Mins),Country),0,1)
,Country)

any one please help me to solve this

Thanks,

Raghu.

[#6]

Look at the attached qvw document by milda "Top 10 - in pivot.qvw". look at "Top 10" pivot table. In that, country is not sorted based on 45 th week. The country is sorted from week 40 to week 45. I want to sort based on the latest week records.

How can i do achieve this

-Raghu.

[#7]

Hi Raghu,

you should try this:

IF(
aggr(
rank(total
aggr( sum({$<Week={'wk 45'}>} Mins),Country)
,0,1)
,Country)
<=10,
aggr(
rank(total
aggr( sum({$<Week={'wk 45'}>} Mins),Country)
,0,1)
,Country))

Milda

[#8]

hi Milda,

if you give me your mail id i would send the qvw doc. Here the upload files is not working.

Thanks,

Raghu.

[#9]

Thanks Milda,

Finally i solved my errors. Your expressions helped me to solve my solution.

Thanks again

-Raghu.

[#10]

I'm glad I could help you 🙂

PS.: So I guess you don't need my email address anymore