4 Replies Latest reply on Apr 8, 2016 9:14 AM by Nicholas Hara

    LOD Calc Issues

    Derek Strand

      Hi all,

       

      I've been going around in circles with this for a little while and I hope someone might have some insight to how to solve this problem. I'm attempting to create a dashboard that shows the distribution of user IDs across various demographics. I want to show these by company and compare them across all companies -- please see the attached workbook and I'll step through what I'm doing.

       

      1. The first column is the actual end-goal here. The orange bar is the selected company while the blue bar is the distribution of all companies. I'm accomplishing the latter by using a fixed LOD calc so that it ignores the company filter.

       

      2. The green bar is the same as the blue bar except represented using a count distinct of the user ids instead of a percentage -- there are 12 user ids in my sample dataset. Here, I've fixed the LOD calc to the display dimension for the individual worksheet. My issues with this approach are:

           a. Action filters across my worksheets don't work because the LOD calc is only fixed to the dimension on it's own worksheet. When selecting $50,000-$60,000, the Gender worksheet still reports having 5 females and 7 males. I would like this to update to represent the gender breakout WITHIN this income bracket. In this case we have 2 females and 4 males.

           b. This means that I will have to create many additional measures containing LOD calcs to each demographic dimension. As this dataset is quite large it causes things to run quite slowly with all the count distincts.

       

      3. In order to attempt to get around the limitations of the previous point, I tried creating a calc that was fixed at all possible demographic dimensions. This is when strange numbers start to creep in. As you can see from the default view of the dashboard, none of the counts add up to the 12 individuals in the dataset. I wasn't able to determine where it's getting 8 from in this case.

       

      Simplyfing the view a bit (Dashboard 2) allowed me to track down that this issue seems at least in part related to dimension entries that don't exist in all possible intersections. For example, B Corp has no users in the $70k-$80k range and no females at $40k-$50k, so when they are selected (again, on Dashboard 2) then the totals drop by three as those buckets are excluded from the count.

       

      If anyone can help it would be much appreciated, we've had a couple people banging our heads against the wall on this one for a while!

        • 1. Re: LOD Calc Issues
          Michael Schuster

          I'm having the same issue.  When using LOD calcs across sheets and trying to utilize actions, there is a disconnect between the total data average for aggregations that don't contain all the possible breakouts within the dataset.

          • 2. Re: LOD Calc Issues
            Jeff Strauss

            have you tried looking at order of operations, I'm not sure where actions apply, but maybe it will help.

             

            Evolution of the Order of Operations Diagram

            • 3. Re: LOD Calc Issues
              Derek Strand

              Jeff,

               

              I have. Since I need to filter by company that basically tosses out INCLUDE/EXCLUDE. In my real world example I'm actually lucky that FIXED still respects the context but it should be skipping all filtering (and seems to, aside from dropping anything that doesn't contain the selected intersections of dimensions).

              • 4. Re: LOD Calc Issues
                Nicholas Hara

                Hi Derek,

                 

                I don't know if I'm quite getting your question, but I think I may have found a possible solution.

                 

                result.png

                 

                Have you tried going to the underlying sheets and playing around with adding the actions to context?

                 

                context.png

                If I'm understanding you correctly, you actually want to calculate your values AFTER filtering on Education, Gender, and Income. With FIXED calcs, that means you have to put the filter in context. If that is not the case, could you please let us know what the desired result is for each of the columns in the dashboard?