topic Re: Pivot table sorting in QlikView

Pivot table sorting

Anonymous — Tue, 08 Apr 2014 11:38:28 GMT

I'm pretty new in the whole Qlikview-world and therefore I might have an simple question - well not simple to me

I have created a pivot table which sums students ECTS points for each week every year. (See table below)

As it can be seen the week (ugenummer) is sorted in ascending order, which is correct. But because an academic year goes from september to september the year after, I want to show data starting from week39.

So the sorting has to be week 39 to week 52 and thereafter week 00 to week 38.

Anybody who can tell me how to do that?

I'm aware of the sort possibility in the properties menu - but it only allows me to sort in ascending or descending order - and not to cut the data at a certain point?

Thanks in advance for answers!!

Kind Regards

Maria

Re: Pivot table sorting

Roop — Tue, 08 Apr 2014 11:44:35 GMT

Incorporate the year into the sort (as I presume that they are different).

If this is not the case you can create a small reference table that will just have the week numbers and a different sort order. Incorporate that into your application and use the new sort order for your sorting.

Don't forget to use the same field name for ugennummer.

Re: Pivot table sorting

sujeetsingh — Tue, 08 Apr 2014 12:04:45 GMT

What do you mean by "cut the data"?

What sorting you need?

Re: Pivot table sorting

Anonymous — Tue, 08 Apr 2014 12:21:52 GMT

I want the pivot table to show week 39 "W39" in the first row and then up to week 52. The row after week 52 should be week 00 - even though I know there aren't any week 00 - (but there is in the data set).

The data is futhermore dynamic and not static - so every week I get another week more in the data set and therefore I should also get another week in the pivot table

Re: Pivot table sorting

Roop — Tue, 08 Apr 2014 12:39:21 GMT

Create the following:

Ugennummer_Sort:

Load * inline

[Ugennummer, Ugennummer_SortOrder

W39,1

W40,2

W41,3

W42,4

W43,5

W44,6

W45,7

W47,8

W48,9

W49,10

W50,11

W51,12

W52,13

W01,14

W02,15

etc to .....

W38,52

];

This table will join automatically to your data and act as a reference to the correct sort order. You then sort the Ugennummer by Ugennummer_Sort .... or Max(Ugennummer_Sort).

Re: Pivot table sorting

arjunkrishnan — Tue, 08 Apr 2014 12:51:39 GMT

Hai Frd...

Create two Variables

Ex:(For 0 to 38)->Year Of Week

vWeek1

= '>=' & WeekStart(MIn(Date),0) & '<=' &WeekStart(Min(Date),38)

(For 39 to 52)->Year Of Week

vWeek2

= '>=' & WeekStart(MIn(Date),39) & '<=' &WeekStart(Min(Date),52)

This Is Used In Your Expression

Like:... 1)Sum({<DateField={$(vWeek1)} Field Name) Here Returns 0 to 38 Data are Show

2)Sum({<DateField={$(vWeek2)} Field Name) Here Returns 39 to 52 Data are Show

I Hope u Try This One Very Helpful to U...

Re: Pivot table sorting

Anonymous — Tue, 08 Apr 2014 13:03:27 GMT

I have typed the code in the script and reloaded it. Then in proporties of the table, I have chosen the sort tab and activated ugenummer - activated "expression" and typed max(Ugenummer_sortOrder).

But nothing happens?

Re: Pivot table sorting

Roop — Tue, 08 Apr 2014 13:05:19 GMT

Uncheck the Sort by Text box

Re: Pivot table sorting

Roop — Tue, 08 Apr 2014 13:07:10 GMT

I think that Ugennummer should have been Ugenummer as well. (Too many "n"s). That field should be exactly the same as the field in your fact table - Sorry

Re: Pivot table sorting

Anonymous — Tue, 08 Apr 2014 13:21:25 GMT

now it is sorting, but in a very starnge way:

Re: Pivot table sorting

Anonymous — Tue, 08 Apr 2014 13:22:46 GMT

Ahh, I did'nt see that - now it works!!! THANKS!