topic Re: Convert Table Expression to Text Box in QlikView

Convert Table Expression to Text Box

cbaqir — Thu, 02 Aug 2018 21:54:13 GMT

I have a table with the following:

DIMENSIONS:

DEFECT_ID

=if(match(COM_CAT,'Solution Design Group')>0,date(COM_CAT_START_DATE))

=if(match(COM_CAT,'Solution Design Group')>0,date(COM_CAT_END_DATE))

EXPRESSION:

sum({$<[COM_CAT]= {'Solution Design Group'}>}date(COM_CAT_END_DATE))-sum({$<[COM_CAT]= {'Solution Design Group'}>}date(COM_CAT_START_DATE))

The numbers have been validated (Avg of 142 days). I need to convert this chart into a text box to be used as a KPI and I can't figure out the right syntax...

Something with AVG(AGGR) I would assume but this is NOT right:

=avg(aggr(sum({$<[COM_CAT]= {'Solution Design Group'}>}date(COM_CAT_END_DATE))-sum({$<[COM_CAT]= {'Solution Design Group'}>}date(COM_CAT_START_DATE)),DEFECT_ID))

Any help is appreciated! Thanks!

Re: Convert Table Expression to Text Box

sunny_talwar — Fri, 03 Aug 2018 02:14:27 GMT

May be this

=Avg(Aggr(Sum({$<[COM_CAT] = {'Solution Design Group'}>} COM_CAT_END_DATE) - Sum({$<[COM_CAT] = {'Solution Design Group'}>} COM_CAT_START_DATE), DEFECT_ID, COM_CAT_START_DATE, COM_CAT_END_DATE))

Re: Convert Table Expression to Text Box

cbaqir — Wed, 08 Aug 2018 21:41:16 GMT

Here is a sample file. When I apply your expression I get -12736.

Re: Convert Table Expression to Text Box

sunny_talwar — Thu, 09 Aug 2018 10:37:17 GMT

Where do I see this -12736 number?

Re: Convert Table Expression to Text Box

cbaqir — Thu, 09 Aug 2018 14:49:25 GMT

I think the selections need to be cleared.

Re: Convert Table Expression to Text Box

sunny_talwar — Thu, 09 Aug 2018 15:16:55 GMT

Try this

=Avg(Aggr(Sum({$<[COM_CAT] = {'Solution Design Group'}, COM_CAT_END_DATE = {"*"}, COM_CAT_START_DATE = {"*"}>} COM_CAT_END_DATE) -

Sum({$<[COM_CAT] = {'Solution Design Group'}, COM_CAT_END_DATE = {"*"}, COM_CAT_START_DATE = {"*"}>} COM_CAT_START_DATE), DEFECT_ID, COM_CAT_START_DATE, COM_CAT_END_DATE))

Re: Convert Table Expression to Text Box

cbaqir — Thu, 09 Aug 2018 21:18:29 GMT

thank you!