Retrieve Min/Max/Avg from Running Total

[Anonymous]

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?

[Kushal_Chawda]

what is expression for rolling total?

[sunny_talwar]

May be use these:

Min(Aggr(YourExpression, YourDimensions))

Max(Aggr(YourExpression, YourDimensions))

Avg(Aggr(YourExpression, YourDimensions))

[Anonymous]

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

[Anonymous]

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)))

[sunny_talwar]

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]

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

[sunny_talwar]

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

[Anonymous]

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.

[sunny_talwar]

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 .