Using the Calc Designer to calculate change for a number of elements by compare day
I am a relatively new user of BI Office and I need simple explanations. :)
I am having a problem creating a custom element that will calculate the percentage change from one year to the next on the same comparison day. For example, I want to compare the enrollment from one fall term to the previous fall term at the same time before the first day of class (Enrollment Compare Day -21). And I want to put the change beneath the compare dates on the rows.
I can make it work for one column element at a time but it does not limit itself to my slicer selection and gives me 100% and Null values for every other day of the year. Although I kind of make it work for certain elements I cannot make it work at all for Officially Enrolled Students (I was told "Because the Officially Enrolled Students is a distinct count of students and you can only get to the distinct through calculating it in the OLAP cube.") and I get a recursive error message.
I am including a screenshot of my report so that it provides context. I have several questions and I am sorry for the lengthy post.
1. How can I calculate the change of an element that is calculated through the OLAP cube?
2. How can I make the grid be limited to my slicer selection?
3. How can I make the custom element work for more than one column item at a time on the same row?
Any help at all in getting me pointed in the right direction is helpful.
Unless I am missing something significant, it is simply a matter of creating a % Growth calculation using 2016 Fall and 2017 Fall as the input members to the calculation.
Using the Pyramid Demo cube in this instance I have selected 2016 and 2017 form the date element tree, then right click on one of the selected members and choose the growth % option:
Adjust your formatting as necessary.
Note: As we are performing a % Growth calculation on cube calculated measures (in this case Net profit and Net Margin) the solve order should be increased to ensure that the % Growth calculation takes place AFTER Net Profit and Net Margin are calculated.
Note: declare the parent of the calculated member to be the same as the parent of the years selected so that it appears on the same level.
Then select the calculated member alongside your years in the rows, in this case also sliced by Manufacturer:
Of course, you can make this all more dynamic by using Parameters to feed the selected years into the calculation.
Hope that helps point you in the right direction.
Rusty McLouth I'm not sure I understand your problem, it may well be that I need to know much more about your data model to be able to solve it.
My understanding is that you need to show the % Growth from one year to the next for a specific academic term, a specific number of days before the term starts. Calculating the % Growth as I described, then slicing by the number of days before term starts, should work.
I'm a little confused by your statement that you lose the actual date year to year comparison if you only select the Fall term members.
I'm not sure how much more I can help without seeing the data model and understanding the dimensionality you are using.Reply
Ian Macdonald I am referring to the actual date of the -21 day, for example. If you look at the grid below, my innermost element is the actual compare date (08/01/2016 and 08/02/2017). If I try to do a % Growth on that element it actually ends up showing me every date in the element, though sometimes I lose it altogether. I am assuming this is because the date changes by the slicer? But, I thought the slicer would control that element and it does not seem to. Alternatively, if I could find another way to display those two dates I could calculate the % Growth on just the term.Reply
Hi Rusty McLouth ,
I have some solutions for you, but they are a bit involved and as I do not have access to your model, it may need some tweaking for your specific requirements and model structure.
- Create a parameter that contains the Enrollment Compare Day members
- Create a dynamic set:
- Consisting of the enrolment date for 2016 plus as many days prior to that as you need (equal to the number of compare Day members)
- Filter the dates using Exists and select the compare day parameter
- Create a Parameter, 2016 date, that is populated by the dynamic set (it will have one value in the set)
- Repeat for 2017 dates.
- Select both Parameters (2016 one first) and construct a % Growth calculation using them. Be sure to set the solve order higher than 0.
- Select the two parameters and the % growth to appear on the grid.
You should get something like my view below. As I do not have a compare day dimension, I've user a Variable in its place and used Lag function to move the dates, but it should be similar.
When you change the Compare Day parameter, it drives the two sets to give one date in each, which in turn are fed into parameters from which the % growth calculation is performed.
If this is all a bit confusing, happy to host a quick web call to walk you though it. Bear in mind I am on UK time (BST) :-)