What is the best function on the below use case: TimeSum or MovingSum ? (For best performance)
Generally it is suggested to use the TimeSum function if my target is not dimensioned by time.
If my target is dimensioned by time, it is better to use MovingSum. But MovingSum doesn't allow start period parameter other than the number data type.
What can we do if my target is dimensioned by time and my start period parameter is month data type and not a number. Should we use TimeSum or MovingSum (after converting month to number)? Which is best for performance?
I attached sample working in the excel where I am achieving the desired result with multiple ways. But would like to get your suggestion to get result by keeping performance as a top priority.
This is definitely a good option and helps for sure in reduction of calculation time as we have only year now rather than the months.
Or if I still want to avoid dimensioned by time completely, can we do something like below. (Just a thought process and I need to test it yet)
I have seen in many of your videos that you bring up a screen with the performance results in milliseconds. I guess you are doing it with the help of Splunk report. Is there any similar scientific way that we can test this performance among multiple options from our end? This may really help us to do R&D and fix the best options.
Or generally do you have any other tips for model builders to measure the performance after changing a particular formula (even with the stop clock is fine). But how to measure this since the impact of changing one formula maybe very low? By opening model or entering data which triggers that calculation? Any suggestions are helpful.
Yes, I like your alternate solution without Time, but you have to be careful with rolling the model calendar, that is why, in this case, it actually might be better to use the dimensionalized way. But if you do that, please make sure your dimension is at the year level and not the month level.
As for timings, it is actually fairly difficult without the aid of Splunk. But, if you are using Chrome, and I assume other browsers have this option as well, go to the three dots in the upper right corner.
This can help you track data entry and to see how long it takes for data to return. Again, it is not exact, but it can help.