Convert Table Expression to Text Box

[cbaqir]

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!

[sunny_talwar]

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

[cbaqir]

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

[sunny_talwar]

Where do I see this -12736 number?

[cbaqir]

I think the selections need to be cleared.

[sunny_talwar · Accepted solution]

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

[cbaqir]

thank you!