Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor III

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
Highlighted

Re: Retrieve Min/Max/Avg from Running Total

what is expression for rolling total?

Highlighted

Re: Retrieve Min/Max/Avg from Running Total

May be use these:

Min(Aggr(YourExpression, YourDimensions))

Max(Aggr(YourExpression, YourDimensions))

Avg(Aggr(YourExpression, YourDimensions))

Highlighted
New Contributor III

Re: Retrieve Min/Max/Avg from Running Total

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

Highlighted
New Contributor III

Re: Retrieve Min/Max/Avg from Running Total

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

Highlighted

Re: Retrieve Min/Max/Avg from Running Total

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.

Highlighted
New Contributor III

Re: Retrieve Min/Max/Avg from Running Total

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

Highlighted

Re: Retrieve Min/Max/Avg from Running Total

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

Highlighted
New Contributor III

Re: Retrieve Min/Max/Avg from Running Total

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.

Highlighted

Re: Retrieve Min/Max/Avg from Running Total

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 .