SUMPRODUCT function

#1

Hi All,
My client has defined a mass average as SUMPRODUCT(,) / SUM() over a lookback period. This is actually a highly used algorithm in the mining industry.

I am trying to replicate the SUMPRODUCT functionality from excel. Has anyone already done this? Or a SUM() over a lookback period - similar to a moving average but using sums instead.

Cheers,
Jen

#2

@jennifer.claughton
i have had to do some non standard sum and averages, might not be same as yours but how to get it done might be.
but before we get into how I do it make sure what you need is not already in Dundas for you:
https://www.dundas.com/Support/learning/documentation/analyze-data/formulas/list-of-formulas
if it is not there then go to this idea.

In the metric set you can edit the Measure and change the sum Formula.
image
I have in mine a whooper Formula I not even sure i understand it any more and I wrote it (It has an If else statement in it!)
your loop back period value might be gotten by a Period Or Period measure that you should be able to reference in the override sum formula.
You are just going to have to play around with it until you get it, with out a good data sample and end result I not sure how else to help having not done this kind of sum before myself.

1 Like
#3

If this is a highly used algorithm, I would be happy to implement it as a Dundas formula, and then you’ll get in one of the next minors. Just point me to the corresponding documentation (or send me the description).

3 Likes
#4

Hi James,
I am very curious what the syntax looks likes for the if else statement within the formula area. I cant seem to get this correct. Can I please have an example?
Thanks,
Jen

#5

this one is looking to see if some column values are zero otherwise i get a divide by zero issue
if ($DirectUtilization$== 0 && $BUD_DRCT_UTIL_PRCNT$ == 0)
{
return 0.0;
}
else if ($BUD_DRCT_UTIL_PRCNT$ == 0 && $DirectUtilization$ > 0)
{
return $DirectUtilization$;
}
else
{
return $DirectUtilization$/$BUD_DRCT_UTIL_PRCNT$;
}

this one is a sum over ride another if sum is zero
var cumlativeDirect = 0;
if ((SUM($Direct$) + SUM($Indirect$) + SUM($B&P$) + SUM($G&A$) + SUM($Other$)) == 0)
{
cumlativeDirect = SUM($Direct$) / 1;
}
else
{
cumlativeDirect = SUM($Direct$) / (SUM($Direct$) + SUM($Indirect$) + SUM($B&P$) + SUM($G&A$) + SUM($Other$) - SUM($OverTimeHours$);
}
return cumlativeDirect;