Best Of

Hi there, I think the answer may depend if you are using a custom view or a modules saved view in the UX page. Within the module views, there are additional configuration options available to apply the sort to specific levels and to also designate the position of the summaries/totals.

See below for screenshot example of how the sort configuration in the module differs from that of the UX custom view. In this example, summaries are being grouped at the top in their natural list position and only lowest level child points are sorted below.

@cbrookes

I could be wrong, but it could be the Previous in the ELSE statement. Previous works earlier because it is getting the previous Day/Cumulate value and I think you are using this previous statement to go up the Tenure hierarchy. Correct?

@cbrookes I agree with Rob, I think this is one where without seeing it, I don't know of a great option off the top of my head. Is the circularity coming in when you reference in the Attrition Forecast into the Remaining Tenure mix Line item?

@rob_marshall Hah! If people need to be careful about my solutions, I will just tell them that I learned everything I know from you, so be careful what you wish for! 😂

@cbrookes

Wow, you did your homework, but you always have to be careful following what @jasonblinn has to say. Obviously, I am kidding about Jason, just seeing if he will respond.

Without seeing your model, it is going to be hard to figure out, but there might be a chance to use a lookup for the previous member if it is stored in a SYS module.

You up for a call to discuss?

@cbrookes

Ok, here you…in the module where you have your attrition percentages, that is my "Russ" module which will contain the final Attrition number when we are done. On the rows, will be your "tenure" list.

Create a SYS Time Days module dimensionalized by Time, at the Day level. We will use the day of the month as our tenure, in this case, 1-10, which will work until you have more than 28 tenures.

Create a module (Calc Russ). The only dimension/list I am missing from the module above is the Tenure list, but have replaced that with the Time, at the Day level. Why the Day level, because you have tenures more than 12 and we need to make sure we have encompassed all tenures. Also, I created a Time Range (One Year) so this only spans one year which will help with space and performance. Again, we are using the Time dimension to do the calcs and since each Native Time period is its own block of data, we will be able to use the Previous function.

Note, if you are using Polaris, you won't need to do that as Polaris will allow you to get the Previous/Next member of a list.

Data: Russ.data[LOOKUP: SYS Time Days.Month, LOOKUP: SYS Time Days.Tenure]
Cumulate: IF Data = 0 THEN 0 ELSE IF Data + PREVIOUS(Cumulate) >= SYS Global.CBrookes Threshold THEN Data ELSE Data + PREVIOUS(Cumulate)

Now that we have the data, we need to get it back to our "Russ" module, but we need to link the above module (dimensionalized by Day) to a module dimensionalized by Month. Before we do that, we need to create a couple of line items in our SYS Time Month module.

In your SYS Tenure (my SYS Test Upload Module) you will need to create a number representing the Tenure, we will use this later to represent the DAY.

Now, create a module dimensionalized by Time (Month) and Tenure with one line item.

Day: DATE('SYS Time - Month'.YearNum, 'SYS Time - Month'.MonthNum, SYS Test UpLoad.Number)

Back to our "Russ" module, create a line item (Attrition) that will get the Cumulate line item from the Calc module using the "mapping" module above. Remember, we can only use two formats in lookups, list formatted line items and Date/Time Periods.

Hope this helps and is clear,

Rob