1 Reply Latest reply on Aug 18, 2016 8:16 AM by David Li

    Build a metrics based on two datasource then aggreagte

    BAIYANG XIANG

      One data source (oracle) : Create Date, Close Date, Case Number (oracle)

      Another Data source (excel) : Transfer date, Case Number(excel)

      ** not every case has a transfer date, that's why i need to build the metrics

      ** the granularity is the same : case level

       

      I blend the two date source based on case number. The metrics (case life) calculation is like this:

       

      if case number(oracle) = case number(excel) then

      datediff(create date, transfer date) + datediff(transfer date, close date)

      else

      datediff(create date, close date)

      end

       

      of cause, i cannot do that because it is not join. i have to add attr or any other aggregation in front of transfer date and all of it to make it work so the whole calculation is aggregated now.

      As you can see, the calculation is calculated on case level. so i will use the calculation in the future with avg/median...

      but tableau now cannot do that because the calculation is already aggregated......

       

      is there a way i can just add the transfer date to primary oracle date set based on case number? if not, how can i do the calculation of case life for each case?