8 Replies Latest reply: Jul 3, 2017 8:27 AM by Brent Smits

# 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?

• ###### Re: Sorting in pivot tables (using calculated expression?)

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.

• ###### Re: Sorting in pivot tables (using calculated expression?)

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?

• ###### Re: Sorting in pivot tables (using calculated expression?)

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)

• ###### Re: Sorting in pivot tables (using calculated expression?)

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

• ###### Re: Sorting in pivot tables (using calculated expression?)

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 ?

• ###### Re: Sorting in pivot tables (using calculated expression?)

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

• ###### Re: Sorting in pivot tables (using calculated expression?)

Yes, you are right.

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

• ###### Re: Sorting in pivot tables (using calculated expression?)

Mh, no problem