topic Re: Text box expression in QlikView

Text box expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

i have attached a sample qvw.

It has a pivot table with some calculations by product number.

My last expression 'Resp Diff' is a time difference.

i need to display the average time of that 'Resp Diff' (as a summary level) in a text box.

Please someone help me with the expression

Re: Text box expression

sunny_talwar — Fri, 23 Dec 2016 13:34:20 GMT

May be this?

=Interval(Avg(Aggr(if(

if(Category='Red',time_effective-clock_start, if(Category='Blue',time_effective-Actual_clock_start))> (first_unit_time-Actual_clock_start) and

time_effective_flag=1,

if(Category='Red',time_effective-clock_start, if(Category='Blue',time_effective-Actual_clock_start))-(first_unit_time-Actual_clock_start)), ProductNumber, Category, Type)))

Re: Text box expression

sunny_talwar — Fri, 23 Dec 2016 13:37:26 GMT

Slight better expression:

=Interval(Avg(Aggr(If(

RangeSum(time_effective, -Pick(Match(Category, 'Red', 'Blue'), clock_start, Actual_clock_start))> (first_unit_time-Actual_clock_start) and

time_effective_flag = 1,

RangeSum(time_effective, -Pick(Match(Category, 'Red', 'Blue'), clock_start, Actual_clock_start), -(first_unit_time-Actual_clock_start))), ProductNumber, Category, Type)))

Re: Text box expression

Gysbert_Wassenaar — Fri, 23 Dec 2016 13:46:30 GMT

Personally I'd just change the chart type of that table to Straight Table and set the Total Mode of the last expression to Average of Rows.

Re: Text box expression

Fri, 23 Dec 2016 13:55:35 GMT

thank you so much sunny

Re: Text box expression

sunny_talwar — Fri, 23 Dec 2016 13:58:50 GMT

No problem