Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
what is expression for rolling total?
May be use these:
Min(Aggr(YourExpression, YourDimensions))
Max(Aggr(YourExpression, YourDimensions))
Avg(Aggr(YourExpression, YourDimensions))
I am not sure if this will work as the rolling total is derived from other columns as well?
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)))
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.
Could I still use Column(#) reference or will this also not work?
Column() function only works within the chart. It cannot be used in another object.
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.
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 .