Rather complex calculated dimension - how to simplify and sort?

[datanibbler]

Hi,

I have an app that compares two sets of data. Each one has several fields that can be compared:

- an ID

- a time_field

- a calculated turnover (that is what I ultimately want to compare) - the turnover is either calculated based on that time_field or, where it's missing, based on volume.

=> I have to go in consecutive steps to compare the two datasets:

- In the 1st step, I analyze whether the ID is present

     - only in A or

     - only in B or

     - in both

- In the 2nd step, I use only that subset where the ID is present in both and I compare whether the times are present

     - only in A or

     - only in B or

     - in both

- In the 3rd step, I use the subset where those times are present in both and compare them.

That makes for a pretty complex formula (I use a calculated dimension on the GUI for that chart). I use three nested PICK(MATCH()) functions for it, that is still a lot easier (rgd. the brackets) than using an IF_construct for the same.

The chart seems to work and looks all right. My issue right now is that the different possible reasons which form my dimension are not sorted. I guess the order in which they appear depends on the order (inside-out) in which I have built the formula.

There is necessarily some remainder (a NULL value in my dimension), that is the remaining difference that is attributable to some other reason. The problem is, that remainder is not clickable - I'd like the user to be able to click any of the reasons - including that remainder - and switch to the sheet with the detail_data_table and have a closer look at those items.

=> Is there any way I can make that clickable? I might get to the point of not having a remainder, but it's not sure that that will remain the same going forward - my app will do that comparison every month and one never knows ...

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer · Accepted solution]

Hi DataNibbler,

I think for the sorting issue you need a dual() for your calculated dimension which returned within the string-part your dimension and within the numeric-part your sort-order. I mean something like this:

Re: Aggr(sum in calculated dimension

Your NULL within the dimension need to be replaced to be clickable. There are various approaches to replace this, one is this: Filling Default Values Using Mapping | Qlikview Cookbook

- Marcus

[datanibbler]

Hi Marcus,

DUAL() is a good suggestion. I will try that. And I'll have a look at your link.

Right now I'm trying to add some more possible reasons to my dimension though they may appear somewhat strange - it is still a start for analyzing the differences - but I guess there will still be a remainder.

Thanks a lot!

P.S.: That article is a bit hard to read because some software is missing on my laptop, the pics are not displayed. I can guess what they show, though. This method does look cool, but not really simpler than putting an IF_construct (using LEN() or anything to find out empty spaces) into the LOAD itself.

I think, however, I have solved the issue. I can just insert into that calculated dimension all possible data_constellations and make sure there can be no records not matchin any of them - that way there cannot be a remainder.