Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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
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
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)))

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
Not applicable
Author

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

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 .