Crosstab not sorted properly with Data Reduction

[ramonarcusa36]

Hi you all.

We're experiencing a strange (and hilarious) issue related to sorting a crosstab chart. Please see attached sample to clarify things (IMPORTANT: log in as User1, otherwise document won't open because it has Section Access). We're working with QV11 SR2.

Basically, we have a crosstab chart with products in rows, years and months in columns. We just show one expression called Sales Amount.

We would like to sort our table by Current Year Sales. I mean, the top 1 product will be the one with the most Sales Amount in Max(Year). So far, this is easy to achieve, just by typing the proper expression in the Sorting tab of the chart object: "=Sum({$<MyYear = {$(=Max(MyYear))}>} [Sales Amount])".

That expression works... unless we have data reduction through Section Access. Users getting reduced data (in my attached example, the user called User1 is just allowed to see Manager Code = 458), will see a weird behavior: products with Null values for the current year Sales (that is, products sold the previous year but not the current year), are wrongly placed on top. This strange behavior just occurs when opening the document with IEPlugin and after data reduction. Table is always sorted perfectly on QlikView client (missing/null values are placed at the bottom).

Let me add one more thing (I'm almost done, I promise): in my example, you'll guys see I'm using a cyclic group for products. It's just for you to see that if the product dimension is extracted from the Fact Table (field called "Product Name in Sales"), the table is always sorted perfectly (even with data reduction and IEPlugin). It's just the "Product Name in Products" (field extracted from Products Table) that is wrongly sorted. A quick answer would be: "then, stay with Product Name in Sales and get rid of Product Name in Products". I can't do that. Don't forget this is just a simplified sample: in my actual QV document, I have a bunch of tables with a lot of fields, so I can't even consider including all those descriptions in the fact table.

Please, can you help me to get my table properly sorted? Sorry for the length of this post, but I thought this issue was worth a deep explanation.

Thanks in advance.

[stevedark · Accepted solution]

Hi Ramon,

I am not seeing the behaviour that you are seeing here - probably due to not being able to run through IE plugin at the moment.

Pivot sorting is not always an exact science though (due to multiple dimensionality).  What you may find works is to use an Alt statement around the expression to change the nulls to zeros:

=Alt(Sum({$<MyYear = {$(=Max(MyYear))}>} [Sales Amount]), 0)

Or you could try bringing in all prior years sales into the sort order also, but give them less weight to the current year ones, by multiplying the current years by a big number:

=(Sum({$<MyYear = {$(=Max(MyYear))}>} [Sales Amount]) * 1000000) +

   Sum({$<MyYear-={$(=Max(MyYear))}>} [Sales Amount])

Note the minus symbol before the equals in the second part of the Set Analysis.  This will provide a better sort order when you roll over to January of next year anyway.

Hope that helps.

Steve

[ramonarcusa36]

Great answer, Steve!

I'm using your second approach (the one that brings in all prior year sales...), and it works perfectly.

The first one doesn't work for me, probably because Alt replaces Nulls with Zeros, but it looks like it can't replace Missing values. Anyway, my problem is solved with the prior years stuff.

Thanks a lot!