Solved: Date Formula

Anonymous · ‎06-22-2021

Hello!

I am trying to figure out a formula but I can't seem to get it right.

I have two tables that have two different types of calls. One is showing Hardware Calls and the other is showing our In House Providers calls. Both tables have a Store Number, Call Date, and a Completion Date.

I want the formula to say if the Hardware Call Date< ( is before) In House provider Completion Date and the Hardware Completion Date> (is after) in house provider Completion Date, return the number 1.

I am trying to find out how many times the in house provider would have been able to complete hardware calls at that particular store (missed opportunity). The struggle I am running into is that these are in two different tables, the common value would be the store number in both tables. Everything I try to do this I get the error code saying there are multiple values where a single value is expected. Any help would be appreciated! Thanks!

v-yingjl · ‎06-24-2021

Hi @Anonymous ,

First as the error message show, please make sure the data type of both of your two dates columns which are used to compare are date type.

In addition, if you want to use 'AND' condition in the if statement, it should be '&&' instead of '&', '&' is used to combine two columns.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

vanessafvg · ‎06-22-2021

are you able to share what code you have written?

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Anonymous · ‎06-22-2021

v-yingjl · ‎06-24-2021

Hi @Anonymous ,

First as the error message show, please make sure the data type of both of your two dates columns which are used to compare are date type.

In addition, if you want to use 'AND' condition in the if statement, it should be '&&' instead of '&', '&' is used to combine two columns.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous · ‎06-25-2021

I made those changes and I am just getting blanks now. Is there another way to write this? I feel like crossing tables is ruining this. The Service Crew has been to all of these stores multiple times, but I only want it to return the date if it was in between the Hanging Hardware Call Date and Completion Date.

v-yingjl · ‎06-27-2021

Hi @Anonymous ,

Since the columns are come from two different tables, you neeed to create measures by the previous modified formula and use a table visual in the report to show the result instead of creating a calculated column directly.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.