4 Replies Latest reply on Mar 2, 2012 8:22 AM by Jonathan Drummey

    Table Calculations on Dimensions?? Possible?

    Andrew Thacker

      I have some weekly logs about the status of some sales prospects.

      The status uses the values A1, A2, B1, B2 etc.  ie they are text.

       

      I want to create a report that looks whether the current value of a project's status, is different from the previous weeks value.

      Table calculations are great for doing this sort of comparison with measures, but I'm struggling with how to do it for dimensions?

       

      Any ideas?

      Thanks

        • 1. Re: Table Calculations on Dimensions?? Possible?
          Jonathan Drummey

          Create a calculated field that does something like IF ATTR([Status]) != PREVIOUS_VALUE(ATTR([Status])) and you should be all set. Any aggregation applied to a discrete dimension effectively creates a measure that you can use in other calcs.

          • 2. Re: Table Calculations on Dimensions?? Possible?
            Jonathan Drummey

            It turns out I was wrong in suggesting PREVIOUS_VALUE() - hat tip to Joe Mako for the correction! 

             

            I'd gotten LOOKUP() and PREVIOUS_VALUE() confused about what they returned. The proper calculation would be something like IF ATTR([Status]) != LOOKUP(ATTR([Status]),-1) THEN "Changed" ELSE "Same" END. The LOOKUP(ATTR([Status]),-1) actually checks the value of  ATTR([Status]) in the previous row in the partition, which is what we want.

             

            The PREVIOUS_VALUE() function is self-referential and effectively recursive. The value provided to the function (between the parentheses) just defines what's returned for the first row of the partition, for the rest of the partition PREVIOUS_VALUE() returns the value of the *entire calculation* from the previous row. Here's a table showing how these different calculations work, for a series of five statuses (these are the status values for "Cameron" in the Status Crosstab worksheet in the attached workbook):

             

            ATTR([Status])PREVIOUS_VALUE(ATTR([Status])) (on its own)PREVIOUS_VALUE(ATTR([Status])) (as used within the IF calculation)IF using PREVIOUS_VALUE (wrong)LOOKUP(ATTR([Status]),-1)IF using LOOKUP (right)
            A1A1A1Same (A1 = A1)NullSame
            A2A1SameChanged (A2 != Same)A1Changed
            A2A1ChangedChanged (A2 != Changed)A2Same
            A3A1ChangedChanged (A3 != Changed)A2Changed
            A4A1ChangedChanged (A4 != Changed)A3Changed

             

            The attached workbook has some other examples showing how PREVIOUS_VALUE works.

             

            Jonathan

            3 of 3 people found this helpful
            • 3. Re: Table Calculations on Dimensions?? Possible?
              Andrew Thacker

              Jonathon

              Many thanks for your responses.

              I was indeed struggling with the your first suggestion .. and had given up.

              By I shall try your new solution.  Really appreciate the example too.

              Andrew

              • 4. Re: Table Calculations on Dimensions?? Possible?
                Jonathan Drummey

                Let me know if it works for you!

                 

                Jonathan