Is it possible to do a calculated column using values from 2 tables?

Hi,

So I have to do a KPI that displays the the average case age vs the average case close time. Unfortunately the open cases table and the closed cases table are separate. My original plan was to do a calculated column in the Open Cases table that for each case calculated:

If(Case Age > [ClosedCase].AvgCloseTime,1,0)

But for some reason the calculated column doesn't let me do that. 

The KPI expression seems to handle multiple tables ok but when I try to do an expression of:

Count(If([OpenCase].[Case Age]>[ClosedCase].[AvgCloseTime],1,0)) it tells me All Arguments of the function > in the expression must be aggregating when the visualization data comes from different data tables.

Is there a better way to do this comparison? 

Thanks

2 Comments

Unfortunately I was not able to find a solution. The requestor changed the requirements though so I was ok :) sorry I couldn't be more help.

id013 - Aug 08, 2018 - 11:24am ::

Did you ever find a solution for this error?  I'm doing something similiar where I have a calculated value that is trying to use info from two different tables.

Same as you've desribed... it recognizes the formula and fields from different tables, but I get an "All Arguments of the function '=' in the expression must be aggregating when the visualization data comes from different data tables" error.

dbrothers - Jul 25, 2018 - 2:02pm ::
+ Add a Comment

(1) Answer

Login