topic Re: Retrieve Min/Max/Avg from Running Total in QlikView

Retrieve Min/Max/Avg from Running Total

Anonymous — Wed, 05 Aug 2015 12:44:42 GMT

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?

Re: Retrieve Min/Max/Avg from Running Total

Kushal_Chawda — Wed, 05 Aug 2015 12:46:32 GMT

what is expression for rolling total?

Re: Retrieve Min/Max/Avg from Running Total

sunny_talwar — Wed, 05 Aug 2015 12:47:26 GMT

May be use these:

Min(Aggr(YourExpression, YourDimensions))

Max(Aggr(YourExpression, YourDimensions))

Avg(Aggr(YourExpression, YourDimensions))

Re: Retrieve Min/Max/Avg from Running Total

Anonymous — Wed, 05 Aug 2015 12:50:28 GMT

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

Re: Retrieve Min/Max/Avg from Running Total

Anonymous — Wed, 05 Aug 2015 12:51:23 GMT

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

Re: Retrieve Min/Max/Avg from Running Total

sunny_talwar — Wed, 05 Aug 2015 12:51:47 GMT

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.

Re: Retrieve Min/Max/Avg from Running Total

Anonymous — Wed, 05 Aug 2015 12:53:42 GMT

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

Re: Retrieve Min/Max/Avg from Running Total

sunny_talwar — Wed, 05 Aug 2015 12:58:22 GMT

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

Re: Retrieve Min/Max/Avg from Running Total

Anonymous — Wed, 05 Aug 2015 13:01:16 GMT

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.

Re: Retrieve Min/Max/Avg from Running Total

sunny_talwar — Wed, 05 Aug 2015 13:04:18 GMT

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 .

Re: Retrieve Min/Max/Avg from Running Total

Anonymous — Wed, 05 Aug 2015 13:06:38 GMT

Yes, you're probably right. Quite tedious

For this specific rolling total, there are column values that actually reference the rolling total to determine what to do which will directly affect the rolling total itself. Quite complex and took me many many hours to get working right.

Now I must collapse all that down to a single perfect expression it seems...

Re: Retrieve Min/Max/Avg from Running Total

sunny_talwar — Wed, 05 Aug 2015 13:08:08 GMT

Wish you all the very best

Re: Retrieve Min/Max/Avg from Running Total

Kushal_Chawda — Wed, 05 Aug 2015 13:08:33 GMT

Try to do some calculation is script by joining tables. I think it will shorten your expression

Re: Retrieve Min/Max/Avg from Running Total

Anonymous — Wed, 05 Aug 2015 13:26:23 GMT

The hardest part is that there are inputfields in the straight table that allow the user to perform some simulation to see how it effects the running total. So I cannot move certain things into load script

Re: Retrieve Min/Max/Avg from Running Total

Anonymous — Wed, 05 Aug 2015 14:03:06 GMT

Is it possible to use "Above" and "Below" functions when consolidating my expression?

Re: Retrieve Min/Max/Avg from Running Total

sunny_talwar — Wed, 05 Aug 2015 14:38:42 GMT

With the aggregate function, you can use Above, below in a text box expression as well