Sorting in pivot tables (using calculated expression?)

[Anonymous]

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?

[micheledenardi · Accepted solution]

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.

[Anonymous]

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]

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)

[Anonymous]

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]

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 ?

[Anonymous]

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]

Yes, you are right.

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

[Anonymous]

Mh, no problem

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