Combining and comparing calculated dimensions

[LauraMorris]

I have a dataset as below, ideally I want to do this within a straight table in the dashboard rather than the data load editor:

Table 1

CallID    JobID         Visit Reason     Visit Outcome

1                    1                  New                     Part Ordered

2                    1                 Parts Fit               Complete

(there will be other rows for the call ID but I am only interested in the Part Ordered outcome and the Parts Fit visit reason)

Table2

CallID      Engineer ID

I am trying to get the output:

JobID    PartOrderCallID    PartOrderEngineer   PartFitCallID   EngineerID

I have tried to use the following dimensions, in isolation they work but in the same table they do not:

[Part Order Engineer]=aggr(ONLY({<calltype={'4 Parts Required'}>}engineername),engineername) 

[PartFitEngineer]=aggr(ONLY({< visitcode={'003'}>}engineername),engineername)

but only one of them will display in the table at a time - is this because they are on separate rows of Table1?

Once I have got both displaying I then need to compare them to see if the Ordering and Fitting engineers are the same

[morgankejerhag · Accepted solution]

Maybe like this?

Dimensions: JobID
Expression PartOrderCallID = max({<VisitOutcome={'Part Ordered'}>} CallID)
Expression PartOrderEngineerID = max({<VisitOutcome={'Part Ordered'}>} EngineerID)
Expression PartOrderEngineer = maxstring({<VisitOutcome={'Part Ordered'}>} EngineerName)
Expression PartFitCallID = max({<VisitReason={'Parts Fit'}>} CallID)
Expression PartFitEngineerID = max({<VisitReason={'Parts Fit'}>} EngineerID)
Expression PartFitEngineer = maxstring({<VisitReason={'Parts Fit'}>} EngineerName)

Morgan Kejerhag
Senior Business Intelligence Consultant
Drake Analytics
http://www.drakeanalytics.se

[LauraMorris]

Thank you so much - that's working.