topic Sorting Issue in QlikView

Sorting Issue

Fri, 16 Sep 2011 16:40:59 GMT

I am sorting data from different dimensions into a custom mapped P&L. One line sometimes has data and sometimes the data is missing (Not 0), but I always want it to show up on line 6. When the current Month data is missing the line defaults to last. My sort is as follows:

=if(PnL='Item1',1, if(PnL='Item2',2, if(PnL='Item2',3, if(PnL='Item4',4, if(PnL='Item5',5, if(PnL='Item6',6, if(PnL='Item7',7, if(PnL='Item8',8, if(PnL='Item9',9, if(PnL='Item10',10, if(PnL='Item11',11,)))))))))))

Is there a way to assure that Item 6 stays on line 6 even if current period data is missing? I've tried saying after line 11 that the alternative is line 6 and it still doesn't work.Any advice is greatly appreciated.

Thanks

Sorting Issue

Jason_Michaelides — Fri, 16 Sep 2011 17:19:49 GMT

Sorry Craig, I don't follow what you're trying to achieve. Can you post your qvw file so we can take a look?

Jason

Sorting Issue

Fri, 16 Sep 2011 18:39:40 GMT

I cannot post this particular file, but let me try to explain a little better.

I am doing a custom sort which orders a bunch of objects.

There is one line though which qlikview doesn't recognize and falls to the bottom of my custom sort.

I found the reason qlikview doesn't recognize this:

The data appears like this

Jan Feb Mar Apr May Jun, Jul

233124, 3125, 3214, 1643, #Missing, 1234, 0

The sort is correct for Jan, Feb, Mar, Apr, Jun, and Jul but May results in qlikview not recognizing the data because it is missing and the line item falls to the bottom of the sort. Is there a way to say If(Line=Missing or no data?

Does that clarify the question at all?

Thanks

Sorting Issue

Jason_Michaelides — Fri, 16 Sep 2011 19:19:36 GMT

So you want to sort by month? Still not with you mate! You say sort by a bunch of objects. Do you mean dimensions?

Sorting Issue

Fri, 16 Sep 2011 19:22:37 GMT

I can put together a quick rough draft

Sorting Issue

Fri, 16 Sep 2011 19:46:12 GMT

I have attached a real simple file to show kind of what I'm looking at.

You can sort by the month and there is a dimension with say 7 lines.

I want line 4 to appear as line 1, but the data is missing in the database. This results in qlikview not reading the data in in my real version it only appears as the last line with no sort applied.

So the sort is just a sort of the order in which the dimension is displayed.

Does that make sense?

The probelm is not shown in my file attached but where my file shows #Missing data( that is where my problem is in my personal file).

Thanks

Sorting Issue

Fri, 16 Sep 2011 20:52:25 GMT

The problem was taken care of by switching to a straight table from a pivot table

Sorting Issue

Jason_Michaelides — Fri, 16 Sep 2011 20:54:23 GMT

All sorted then? I haven't had a chance to look at the doc yet.

Sorting Issue

Fri, 16 Sep 2011 21:05:03 GMT

Yes, all sorted thank you very much