topic Need help in pivot table sort in QlikView

Need help in pivot table sort

Tue, 02 Feb 2010 07:20:30 GMT

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.

Need help in pivot table sort

Tue, 02 Feb 2010 14:13:47 GMT

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

Need help in pivot table sort

Tue, 02 Feb 2010 14:56:04 GMT

Milda,

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

Thanks again

-Raghu.

Need help in pivot table sort

Tue, 16 Feb 2010 08:15:44 GMT

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

Need help in pivot table sort

Wed, 17 Feb 2010 14:09:06 GMT

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.

Need help in pivot table sort

Wed, 17 Feb 2010 14:16:16 GMT

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.

Need help in pivot table sort

Wed, 17 Feb 2010 15:36:47 GMT

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

Need help in pivot table sort

Wed, 17 Feb 2010 16:12:11 GMT

hi Milda,

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

Thanks,

Raghu.

Need help in pivot table sort

Wed, 17 Feb 2010 17:18:33 GMT

Thanks Milda,

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

Thanks again

-Raghu.

Need help in pivot table sort

Thu, 18 Feb 2010 07:46:16 GMT

I'm glad I could help you 🙂

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

Need help in pivot table sort

Thu, 22 Apr 2010 12:42:21 GMT

Hi all, can anyone help me. In the top10.jpg I want to show the total of all destinations for the respective week. Currently what happened was the total is showing only for the Top10 destinations, not for all destinations. Use the top 10 - in Pivot table.qvw document. Thanks, Raghu.