SAC Story - How to show YOY % based on dynamic date selection

[KP01]

Hi, 

We have a requirement where user wants to select a Date from Date input control and look at the Headcount  for same date for last 5 years. 

Example- if user selects 3/10/2024, we need to show them headcount for 3/10/2023, 3/10/2022, 3/10/2021 and 3/10/2020

They would also like to see Growth %. Example - For 3/10/2024 Growth % compare to 3/10/2023 headcount, for 3/10/2023 Growth % compare to 3/10/2022 headcount..

What would be the most effective approach to accomplish this task?

Thank you in advance. 

[tylerrose]

One solution I've found to this problem is the custom scripting approach. I've realized that the Date input control offers nice functionality but limited flexibility for advanced requirements like these.

I've included an example from one of my onInitilization scripts below, which gathers MTD dates on a current day. Your question is similar, but would require some tweaks to the logic.

var Dates = ArrayUtils.create(Type.MemberInfo);

for (var i=1; i<=(ConvertUtils.stringToInteger(current_date.getDate())); i++){
if (i<=9) {
Dates.push({id:((ConvertUtils.numberToString(current_date.getFullYear())).concat("-").concat(current_date.getMonth()).concat("-0").concat(ConvertUtils.numberToString(i))),description:""});
} else {
Dates.push({id:((ConvertUtils.numberToString(current_date.getFullYear())).concat("-").concat(current_date.getMonth()).concat("-").concat(ConvertUtils.numberToString(i))),description:""});
}
}

MTDDate.getInputControlDataSource().setSelectedMembers(Dates);

This allows maximal flexibility and these dates can be used to drive custom calculated measures through input controls.

The downside to this method is that you also have to rebuild any logic that was relying on the date input control - this can be a hassle. It's an entirely new way of user date selection.