5 Replies Latest reply on Jul 21, 2018 1:39 AM by simone magagnini

Table Calculations on Dimensions?? Possible?

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?

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?

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

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

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?

Let me know if it works for you!

Jonathan

• 5. Re: Table Calculations on Dimensions?? Possible?

You have no idea how long I have been trying to find a solution for the same problem...I know it is a very old threat but right now for my big problem this SAVED me!

Thanks is not enough for you and all the smart people helping us in this forum! You are all my heroes!