I’ve got two calculated fields:
No of unique Appointments:
distinct_count({Appointment ID})
and
sum of amount:
max(ifelse({Type}=‘FINAL’,ifelse({Evaluation Currency}=‘USD’,{Amount},{Amount}*{Evaluation Rate}),NULL),[{ID}])
Both are working fine on the level of aggregation of the table I want to use them.
However I cannot divide them to be able to get the:
amount per appointment on that same table.
any ideas?
In your table are there any number of appointments that are 0?
Hello Max,
Thank you for taking time to try and help me!
No, I am showing all data based on existing appointments, filtered at the dataset joins directly.
So company A has x appointments on location 1. I am not bringing to the analysis companies or locations that have no appointments.
just to bring some context,
Type: is just a type of document. I need to calculate amounts for both “Final” and “Pro” types, but the logic is the same.
Evaluation currency: the currency on which the amount is saved on the database.
Amount: is the needed output metric (i need this on USD, thus the multiplication with “Rate”
ID : is a specific Id i need to take the max aggregation out of so that i bring the amount to the correct level of aggregation based on my dataset.
Thanks again for helping!
Hi @kvilaras,
To be able to help, it would be great if you could share some more details about your input and expected output (incl. the level of aggregation that you’d like to achieve). Providing a simple input example and how that maps to the expected output table would also be helpful.
Are the described “Appointment ID” and “ID” columns the same or two different columns?
Could you please also elaborate on the requirements/reasoning for the use of the MAX function in the second calculated field?
Thanks!
Hi @kvilaras
We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.