9 Replies Latest reply on Sep 13, 2018 9:58 AM by Ben Perlman

    Converting Measure to Dimension Messing up Data

    Ben Perlman

      I read a blog post on converting measures to dimensions by using fixed LOD calcs.  High level I'm trying to categorize customers by comparing revenue from this year to revenue from last year.

       

      The calculation is as follows:

       

      IF SUM([LTM Collections Revenue Previous Year]) = 0 AND sum([LTM Collections Revenue]) > 0 THEN "New"

       

       

      ELSEIF  [LTM/Previous Year Calc] <.2 THEN "Lost"

      ELSEIF [LTM/Previous Year Calc] <.4 THEN "High"

      ELSEIF [LTM/Previous Year Calc] <.6 THEN "Medium"

      ELSEIF [LTM/Previous Year Calc] <.8 THEN "Low"

       

       

      ELSE "Stable" end

       

      This is producing accurate results, but I am trying to convert these to a dimension so I can count the number of clients that fall into each group, and show that in a table

       

      In order to do this I created the below LOD calc, but it is mis-categorizing the clients.  A lot of "Lost" clients are getting categorized as "high"

       

      IF [Collections Revenue 24 Months] > 0 THEN

       

      (

      IF { FIXED [Client Name] : SUM([LTM Collections Revenue Previous Year]) = 0 AND sum([LTM Collections Revenue]) > 0}  THEN "New"

       

       

      ELSEIF  { FIXED [Client Name] : [LTM/Previous Year Calc] <.2} THEN "Lost"

      ELSEIF { FIXED [Client Name] : [LTM/Previous Year Calc] <.4} THEN "High"

      ELSEIF { FIXED [Client Name] : [LTM/Previous Year Calc] <.6} THEN "Medium"

      ELSEIF { FIXED [Client Name] : [LTM/Previous Year Calc] <.8} THEN "Low"

       

       

      ELSE "Stable" end) ELSE NULL end

       

      Any idea what I am doing wrong here? I am trying to only look at clients who had revenue in the past 2 years, and group them accordingly.

       

      Thanks!

        • 1. Re: Converting Measure to Dimension Messing up Data
          Joe Oppelt

          A sample workbook would go a long way toward helping you here.

          • 2. Re: Converting Measure to Dimension Messing up Data
            Ben Perlman

            Sure, please see attached.  Sorry it took me a few hours to create the dummy workbook as there is company sensitive info in there. Some of the calcs are not loading properly since I had to create a new data source from the existing actual, so some of the calcs came in as strings.  Let me know if you have enough to go off from this.  It took quite some time to get it up

             

            The end result I am looking for is to create a table similar to this:

             

                                                                                         

            Client   Status# ClientsTotal Rev 6/18Avg. Rev 6/18Avg. Rev 6/17
            Stable              2,500      50,000,000            10,813              8,127
            High              1,500        1,500,500                 852              2,800
            Medium              1,300        1,000,000              2,574              4,982
            Low              1,272        5,148,225              4,047              5,593
            New              3,601      10,252,525              1,228                     -
            Lost              6,655           571,942                  86              1,249
            Total            16,828      68,473,192              4,035              3,948

             

            the formulas in the original post are

            • 3. Re: Converting Measure to Dimension Messing up Data
              Joe Oppelt

              All your calcs came across as data source fields, so I can't edit anything to see what's going on in them.

              • 4. Re: Converting Measure to Dimension Messing up Data
                Joe Oppelt

                Did you make the data source in excel?  If so, delete out the columns from the excel file that were actually calcs.  then when you bring in the bogus data source and do "replace data source", the calcs will still remain as calcs and not data source fields.

                 

                Also, maybe just keep 1000 rows, not 18000.  Even 100 rows, if you can be sure to get some of the ones that seem to be misbehaving for you.

                • 5. Re: Converting Measure to Dimension Messing up Data
                  Ben Perlman

                  I did. Here is an updated file.  The calcs are Attrit Risk (as a measure) and Attrit Risk (copy) as a dimension. 

                  • 6. Re: Converting Measure to Dimension Messing up Data
                    Joe Oppelt

                    The problem with some calcs that keep you from making them dimensions is when you have aggregates in them.  But careful use of FIXED LODs lets you eliminate the aggregation element of the calc.  I did that in the attached with [Collections Revenue 24 Months]  For Test Client 1, you have multiple rows with values in that measure.  But  [Collections Revenue 24 Months (copy)] uses FIXED LOD to sum them up and because it's a FIXED LOD you can make it a dimension.  Notice that the value lands on each row for a given Test Client.  So you have to be aware of that when you use it as a measure.  SUM([ that fixed lod ] ) will essentially multiply the value by the number of rows.  So if you use it somewhere as a measure, use MIN or AVG or ATTR or MAX.  But now you can use that at the dimensional level.  Do the same for [LTM Collections Revenue Previous Year], and then when you use the FIXED LOD in another calc, it's already summed, but tableau will still let you treat it as a dimension by the magic of FIXED LODs.

                    1 of 1 people found this helpful
                    • 7. Re: Converting Measure to Dimension Messing up Data
                      Ben Perlman

                      Thank you, Joe!

                       

                      So where I was trying to use LOD's to calc differences of measures, you were using LOD calcs for the initial calcs. 

                       

                      For the way I categorize the risk of clients based on comparing the revenue of the last 12 months to the revenue of months 13-24, does the following make sense? Or is there a better way to do this calc?

                       

                      IF [Collections Revenue 24 Months] > 0 THEN

                       

                       

                      (

                      IF { FIXED [Client Name] : SUM([LTM Collections Revenue Previous Year]) = 0 AND sum([LTM Collections Revenue]) > 0}  THEN "New"

                       

                       

                      ELSEIF  { FIXED [Client Name] : [LTM/Previous Year Calc] <.2} THEN "Lost"

                      ELSEIF { FIXED [Client Name] : [LTM/Previous Year Calc] <.4} THEN "High"

                      ELSEIF { FIXED [Client Name] : [LTM/Previous Year Calc] <.6} THEN "Medium"

                      ELSEIF { FIXED [Client Name] : [LTM/Previous Year Calc] <.8} THEN "Low"

                       

                       

                      ELSE "Stable" end) ELSE NULL end

                      • 8. Re: Converting Measure to Dimension Messing up Data
                        Joe Oppelt

                        In my example I made [Collections Revenue 24 Months (copy)].  In real life I would name that [Collections Revenue 24 Months LOD].

                         

                        And I think you want to do the same sort of LOD for each of those other calcs, and even the components of those calcs.  For instance we have [LTM/Previous Year Calc].  Inside there you have sum([LTM Collections Revenue])/sum([LTM Collections Revenue Previous Year]).  I'm not immersed in your application or business needs here, but I think you want to do each of those individual SUMS at the [Client Name] level like I did in my LOD.

                         

                        Something I almost always do when I'm stringing together compound lines of logic:  I make a sheet like my Sheet 2.  And I display all my calcs and even the components that make up the calcs, on a sheet like Sheet 2.  Make sure you are using the right building blocks when you string them together in your final calc.  In the end you can actually nest LODs.  They don't have to be a hundred separate calcs, but when I'm building things initially I start off with individual calcs.

                         

                        So, for example, if the logic in [LTM/Previous Year Calc] should be one LOD divided by another LOD, at the outset you could have them as separate calcs, and then when you are sure of all the components, [LTM/Previous Year Calc] could be:

                         

                        { FIXED .... } / { FIXED ... }

                         

                        rather than [calcLOD 1]/[calcLOD 2].

                         

                        It would take me a while to sort through all the logic nested in all those calcs to tell you for sure whether your proposed logic is correct.  I don't have the bandwidth to do that.  I'll just say that you should make a temporary Sheet 2 and spell out all the sub-components of the final calc and make sure you're using all the right bricks in the structure you need to build.  You know what the final value should be for some client.  If it doesn't end up calc-ing out to that final number, one or more of your bricks is wrong.

                        • 9. Re: Converting Measure to Dimension Messing up Data
                          Ben Perlman

                          Thanks! I actually just finished changing all of my orignial calcs to LOD calcs.  I'm not sure I'm understanding your display of the calcs worksheet.  Do you happen to have a screenshot or something of what yours looks like? This would be extremely helpful as my approach seems to be pretty disorganized