Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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))
Here is a sample file. When I apply your expression I get -12736.
Where do I see this -12736 number?
I think the selections need to be cleared.
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))
thank you!