4 Replies Latest reply on Jan 2, 2013 6:23 AM by Chip Lynch

    Dimension calculation based on MAX() subquery?

    Chip Lynch

      In a health insurance database, we have Members who may or may not have Claims.  I have a parameter on a slider that is "Limit Number of Months", an integer, between 1 and 24.  It limits my dashboard to Claims with a Service Date in the last however-many months.

       

      So far so good.  I have a handful of charts with various dimensions that all filter by this successfully.

       

      Now, I want to add a chart with the number of Members -- COUNTD(MEMBER_ID)  -- in a stacked bar arrangement where members WITH a claim in the parameterized time period get one color (we call this "Utilization") and members WITHOUT a claim in the time period get another ("No Utilization").

       

      Here's the calculation for "Utilization Flag" (it was a boolean, but ignore that for now):

      IF ISNULL(MAX([Service Date])) THEN 'No Utilization'

      ELSEIF MAX([Service Date]) >= DATEADD('month', [Limit Number of Months]*-1,TODAY()) THEN 'Utilization'

      ELSE 'No Utilization'

      END

       

      MAX() forces this to be a Measure, not a dimension, which I understand, but if I take the MAX() out, then I have a situation where some Members are double counted, since the [Service Date] is tied to the Claim.  That is, a Member can have a Claim in the service period and outside of it, so they would be double counted if I counted distinct MEMBER_IDs grouping by this Utilization Flag.

       

      So I need the MAX(), since if the most recent claim wasn't in the period, then the older ones don't matter.  Now, though, I can't stack the bars because my boolean result is now a Measure.

       

      The best I can do is to set the Level of Detail to MEMBER_ID, and make Utilization a column header.  This works visually (except I'd prefer a true stacked bar), and the numbers are correct, but now if I mouse over it selects every individual member... I have a uni-color stacked bar with thousands of unit-width stacks (note the high number of marks in the screenshot)!  Also, this makes it impossible to calculate and display the "total" number of members.

       

      Lastly, I'm reticent to add another subquery to the process, although my one possibility is to add "latest activity" as a field in the database, but I feel like I shouldn't have to.  The dashboard is filter and drill heavy, so having a single data source is a huge boon.

       

      I can't publish data due to privacy concerns, but I've included a highly aggregated screenshot.

       

      Any ideas are welcome!

        • 1. Re: Dimension calculation based on MAX() subquery?
          donna trinh

          I tried applying the calculated field you listed withe data i have = if isnull(max([LASTUPDATEDPST])) then 'no' elseif max([LASTUPDATEDPST]) >=dateadd('month',[Delay in Minutes]-1,today()) then 'yes' else 'no' end

           

          Please see attached to see if this is what you wanted. 12-24-2012 9-35-47 AM.png

          • 2. Re: Dimension calculation based on MAX() subquery?
            Chip Lynch

            Thanks Donna,

             

            So, the horizontal bars all have the same value -- that seems to not be what I was looking for, unless I misunderstand the data.

             

            I've tried to recreate the problem with Tableau's "Superstore Sales (Excel)" sample data so we have common ground.  I've attached a Tableau Workbook that uses a full extract from that sample data (COUNTD() is not available without the extract) that everyone should be able to use.

             

            Examine the dashboard and in particular the Utilization chart on it.  Visually, it is correct, but the bars are not solid -- we should be able to click the "Utilization" and "No Utilization" bars to filter the rest of the dashboard.  The only way I can get it to work even this well is to make the "Level of Detail" the individual customer, which means that the TOTALS on the horizontal bars don't show up.  This is most evident if you filter on a small state -- the bars become long lists of "1" each.  If I remove the Level of Detail, the chart never displays two columns at all.

             

            Also, I'm not sure the numbers account for NULLs properly... that is, if I have a customer that NEVER purchased anything, they should show up in the "No Utilization" bars.  Compounding this is the "Cutoff Days" filter... my users need to limit the number of days during which utilization is counted.  For example in the attached, anyone who hasn't made a purchase in more than 365 days (or never) is "No Utilization".

             

            What I WANT is this exact dashboard, but with the bars in "Utilization" to be solid and to display the total number of customers, in each customer segment, that have or have not placed an order in the last "Cutoff Days" days.

             

            Thanks for the help!

            • 3. Re: Dimension calculation based on MAX() subquery?
              Shawn Wallwork

              Chip, if I understand correctly, you can do this using a reference line. Take the Count of Customers off the label shelf, put it on the LOD shelf. Then use it to set up a reference line, with the line set to none and the label set to value, by cell. You can aggregate on anything except SUM or TOTAL.

               

              You do run into one of Tableau's little quirks. Tableau tries to save space so it puts the longest bar label inside the bar, which in this case turns out weird looking:

              Chip-1.PNG

              You have two choices. Rt-click and click format. Then go to shading and set it to 0%. This gets rid of the two different orange colors. This works OK:

              Chip-2.PNG

              But if the colors were reversed you'd get this:

              Chip-3.PNG

              Not so nice. So the other option is to pin the axis. In this case a fixed axis range of 0-290 works:

              Chip-5.PNG

              In Beta 8 they are working on solutions to this. They are detecting the color of the bar and then posting black or white labels to try and produce the best contrast. Personally I wish they'd just turn off the 'space-saver' functionality altogether. I think this last image works best and looks best.

               

              Hope this helped,

               

              --Shawn

              1 of 1 people found this helpful
              • 4. Re: Dimension calculation based on MAX() subquery?
                Chip Lynch

                Hey Shawn, this is great -- the totals show up nicely and everything's still drillable.  (Only problem is that I can't see your embedded pictures -- must be a forum issue, but the attached workbook is great).

                 

                There's only one little piece that's still not 100%, but I'm guessing it's impossible.  If I mouse over the Utilization bars, I still see individual Customers all stacked together.  It's a minor issue since all the functionality needed is somewhere, but, for example, to filter by Customer Segment we can no longer select the bar chart themselves; we have to select the row heading.

                 

                Still, getting the totals to display is excellent.  Thank you!

                 

                ---Chip