Calculation from 2 datafiles

[Kirsten]

I assume a calculation can be made in Datamananger: when you click on edit at the datafile below. Add field/calculated field.

However I have 2 datafiles and when I want to create the calculation I need to have 1 metric from 1 datafile and 1 metric from the other datafile. When I select Add Field/Calculated field, I only get the metrics available from 1 datafile. (the one selected in the bubbles in the begin screen)

So the question is how to combine metrics from 2 datafiles in 1 calculation?

See attachment for the screenshots for clarification

[lorenzoconforti]

"When I select Add Field/Calculated field, I only get the metrics available from 1 datafile." - this is as expected. You can only add calculated fields referencing fields in the same table; it makes sense as these are two different tables and, from a data structure perspective, there is no row-to-row correspondence

If you want to overcome this you'll need to either join the two tables or lookup values from one table to the other, form the data load editor

[Kirsten]

Hi @lorenzoconforti 

Sorry for the delayed response. I thought tables were joined when, after data load editor,  you go to datamanager, synchronize tables, and then recommended associations: apply all. I was then able to combine dimensions from the 2 datasets.  But indeed did not combine measures yet, and that doesn't work now. I will go and lookup for some video's to see  how to join with data load editor.

Is the reason that I currently get total values of my measure from dataset 2 in my table, also related to the join that should be performed? See attachment

[lorenzoconforti]

It really depends on the data structure and which field is linking the two tables

can you post a screenshot of the Data model viewer?

It's easier to see in there what's happening

[Kirsten]

I was able to resolve the issue about "having totals only in my table" by simply reloading the data (sometimes it helps if you just write out steps to explain, that resolves issues 🙂

Though I still need to make my calculation (measures from 2 tables) and join tables. I added in the attachment the load script and screenshots of the data model viewer, can you kindly help on how to integrate the join in the script, the one I performed from the video wasn't successful.

See attachment

Kind regards, Kirsten

[lorenzoconforti]

Hi Kirsten, apologies for the delay; to recap. You want to calculate the following:

Sum (CM Hours)/ Count(Calls)

Is that for a single case number (i.e. you want to know the average hours per call for each case number) or for the total of the case numbers (i.e. you want to know on average across all case numbers how many hours per call was spent)?

Lorenzo

[Kirsten]

No problem. Yes per single casenumber, but then in the table it should group in 1 row. See attachment for example.  So 1 additional column with CMHour/Calls.

Just was wondering because the 2 files are already connected/joined on case number. Do I still need to do an additional join? How does that work?

Stay safe and healthy,

Kirsten

[lorenzoconforti]

You wouldn't need to join the table and the calculation should work already as:

Sum(CM hour)/Count(Calls)                       

[Kirsten]

Hi @lorenzoconforti 

It works if you create a calculated field from Data under Analysis

But not when you approach it from Datamanager, and create a calculated field from there. See attachment. Then it doesn't recognize the measure from the not selected datafile and it also somehow doesn't recognize Sum and Count, while the expression is literally taken over from the expressions under data

[lorenzoconforti · Accepted solution]

Correct. It won't work in the data manger (as you would need to join tables; also, in that case it will just be done on a record level, which is not what you are looking for)

It will work in your analysis as a measure