Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Retrieve Min/Max/Avg from Running Total

Hello,

I have a straight table formula expression that uses a dimension of "Date" to create a complex rolling total. The rolling total references other columns within the straight table to accumulate.

I have a requirement to get the Min/Max/and Average values over all dates from this rolling total and use it in another chart.

What's the best way to do this?

15 Replies

what is expression for rolling total?

MVP

May be use these:

Min(Aggr(YourExpression, YourDimensions))

Max(Aggr(YourExpression, YourDimensions))

Avg(Aggr(YourExpression, YourDimensions))

Anonymous
Not applicable
Author

I am not sure if this will work as the rolling total is derived from other columns as well?

Anonymous
Not applicable
Author

Hello -

It's quite complex and references other columns in the straight table, as it some cases it adds before subtracting the total.

But here it is:

AVG_DAILY_DEMAND is a static value in the data load.

"Total Qty of IQ KB" is a calculated field in the straight table (from many other fields)

Column7 and Column2 are other reference colums

"Units Per IQ KB Signal" is a calculated filed in the straight table ( from many other fields)

if(isnull((AVG_DAILY_DEMAND)-Above((AVG_DAILY_DEMAND))),("Total Qty of IQ KB")-(AVG_DAILY_DEMAND),

if(Column(7)>0,

(Above(Column(2))-(AVG_DAILY_DEMAND))+("Units Per IQ KB Signal"*Column(7)),

Above(Column(2))-(AVG_DAILY_DEMAND)))

MVP

I agree, you will need to list out all the expression, instead of using the expression labels. I don't see any alternative to this.

Anonymous
Not applicable
Author

Could I still use Column(#) reference or will this also not work?

MVP

Column() function only works within the chart. It cannot be used in another object.

Anonymous
Not applicable
Author

Yes, that was my fear. Hmm I am not sure how I will do this then as the calculation for the running total relies on many other factors within the chart. I do not believe it would be entirely possible to write these factors into a single formula expression as they derive over the individual date dimensions.

MVP

I somewhat disagree with you on this. Anything that has been done in a chart's total (min, max, avg) can be replicated in a text box object. It may be very tedious, but doable .

Community Browser