2 Replies Latest reply: Apr 24, 2015 8:00 AM by Friedrich Hofmann RSS

    Rather complex calculated dimension - how to simplify and sort?

    Friedrich Hofmann


      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