What is the best function on the below use case: TimeSum or MovingSum ? (For best performance)
Hi,
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.
Best Answer
-
Great question...You are correct, Timesum() can have poor performance when using it with Time in the Applies, but what about this idea.
What if you placed the TimeSum() within your "input" module after the To? Yes, TimeSum() is still technically dimensionalized by Time, but it is only 3 years at the year level.
Then in your target, you get that value, so no YearValue() function.
Does that work for you?
Rob
1
Answers
-
My bad, you will still have to use the YearValue() function. See if the below works for you.
Source Module
Input module
Target Module
Rob
1 -
Thank you for taking time and replying to this.
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.
Thank you.
0 -
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.
Rob
2 -
0