Creating a calculation to provide difference between two columns of numbers
We have reports that use named sets to provide four years of data and need to show the difference between the last two. We've done it with a quick calc using data points but we find that these hard coded data points present an issue when we change categories. For example, we are showing the last four Fall terms of enrollment and via the quick calc have a calculation that subtracts Fall 2020 enrollment from Fall 2019 to show the change. The problem is when we update the slicer to Spring semester. We still see the Fall 2019-2020 difference. Any ideas as to how to do this so that the calculation is updated when we change the term from Fall to Spring?
1 reply
-
It depends on the type of Model you are using as to how it can be accomplished. SSAS models use MDX for writing formulas, while all other types such as SQL Server and IMDB (Pyramid in-memory database) use PQL.
Building this in MDX is fairly simple and straightforward because you can reference the contents of a List/Named Set by its position. For example, if your set always returns 4 years, you can reference the last two by using the Item property of the set. The Item uses what is called zero-based indexing, which means the four items in the list are 0, 1, 2, and 3.
Example:
My Named Set: [Last 4 Periods]
My Difference Calc: [Last 4 Periods].Item(3) - [Last 4 Periods].Item(2)
You will notice in the screenshot above that a custom list will actually be represented by a GUID as opposed to its Caption or Label.
To do this in PQL, you would use the GetElement function. This function requires you to pass in the List and the position.
Example:
My Named Set: [Last 4 Periods]
My Difference Calc: GetElement({[Last 4 Periods]}, 3) - GetElement({[Last 4 Periods]}, 2)
