Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
brentviata
Contributor III
Contributor III

Sorting in pivot tables (using calculated expression?)

Heyy

I made a pivot table calculating some percentages and absolute values. However, I want the table to sort based on the total of the absolute values. So now I have: see screenshot 1

and what I want is that Netherlands is on top, then Belgium, then Germany and lastly France because the total absolute value is the following:

Netherlands: 6424

Belgium: 3518

Germany: 1460

France: 988

I tried using the following as calculated dimension:

=if(aggr(rank(count(field)),land)<=4,land)

But this does not give me the result I want.

field = the field I use for the absolute value.

I use the 4 countries as a filter so I have them selected, I can also ask it to calculate those totals because the columns are pivotted using another field so just removing this field from the dimensions gives me the totals. So how to get Qlikview to sort using these values?

1 Solution

Accepted Solutions
micheledenardi
Specialist II
Specialist II

Hi Brent,

why you can't use simply: count(total <land> field)

and use it on sort tab of your pivot ?

If this won't work, please share some example data.

View solution in original post

8 Replies
micheledenardi
Specialist II
Specialist II

Hi Brent,

why you can't use simply: count(total <land> field)

and use it on sort tab of your pivot ?

If this won't work, please share some example data.

View solution in original post

brentviata
Contributor III
Contributor III
Author

Hey

This works but (and now I am gonna be very annoying) it does calculate it based on the full data. I restarted and made the table now with set expressions where I can change the data using a variable and it will be completely independent from the current selection. Is there a way you can add a set expression in there so that I can say I want to filter using <land> but also field = variable where field is the date field and variable is the month I want the variable to be?

Anyways, thanks already!!

micheledenardi
Specialist II
Specialist II

Previously you have to calculate the month of your date field.

After that you can use them in a set expression on these formula like:

count({<land={'France','Spain','Mexico'}, FieldMonth={'$(vMonth)'}>} total <land> field)

brentviata
Contributor III
Contributor III
Author

Yes, that works perfect!

While working further on this I noticed something and perhaps you could help with that too.

You use {'$(vMonth)'} to indicate the month as a number I think, right?

I use {$(field)} but this only works when I type as value for the variable, the month between " "

so for example: "Jan-2017"

Is there a way for it work (or do you have any suggestions I can try)? I want to type in the month without " " and the following does not work: {'$(field)'} or with " "

Second, do you know how I can use a field as a list of predefined values for a variable?

using = field does not work because it gives me - as output.

Ok the second question I found eventually but it still leaves me with the first question

micheledenardi
Specialist II
Specialist II

Hi,

let vMonth=month(Today());                    //this return jul (string)

let vMonth=num(month(Today()),'00');    //this return 07 (number)


How and where are you declaring $(field)} ? In the layout by using inputfield ?

brentviata
Contributor III
Contributor III
Author

Ok I understand.

I use this as a set expression in my expressions and in my sort expression.

This allows me to select a certain month for a sheet and another month for another sheet without having to use the current selections because current selections are across the entire file, right?

I even made it so that it gives me " field " as predefined values . Not using the " " will give me empty tables

micheledenardi
Specialist II
Specialist II

Yes, you are right.

I'm sorry but without the .qvw i can't help you with the double quotes issue.

brentviata
Contributor III
Contributor III
Author

Mh, no problem

Thanks anyway for your efforts ^^ solved a major problem already!