Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
brentviata
New 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
Valued Contributor

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.

8 Replies
micheledenardi
Valued Contributor

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.

brentviata
New Contributor III

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?

Anyways, thanks already!!

micheledenardi
Valued Contributor

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)

brentviata
New Contributor III

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

micheledenardi
Valued Contributor

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 ?

brentviata
New Contributor III

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

micheledenardi
Valued Contributor

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.

brentviata
New Contributor III

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

Mh, no problem

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

Community Browser