Ok, to set the scene, I have two connections to data: sales and number of customers. It's not be feasible to combine this data together before it gets into Tableau.
I have named all of my columns the same for the dimensions of both connections (and automatic management of the relationships looks good).
The issue that I'm having difficulty with is calculating the sales per customer. Now bear with me because it's not quite that simple... this data is updated daily, but occasionally, one of the sources will fail to update. If one of the sources does fail, one set of data will be complete for yesterday, and the other will not. This means if I calculate the number of sales per customer, that value will either be inflated or decreased artificially when looking over a period such as current month to date.
Initially (when I was only dealing with one connection) I added a MaxDate column (which was a constant) to the SQL query. This MaxDate effectively gave the date at which the query was run, or the date of the most recent sale, whichever was the greater.
I added the same MaxDate column to my second connection. What I would like to do is something either like this:
1. Get the minimum of the MaxDates for the two connections, lets call this MinMaxDate
Filter out any results where either connection's Date > MinMaxDate (because one day's worth of data is useless without the other)
2. Filter out results where Sale Date > Customer MaxDate OR Customer Date > Sale MaxDate
This would prevent me from getting situations where I have 5 days worth of sales for a week but only 4 days worth of customers and getting a skewed calculation.
The issue that I'm having is that for cross connections I can only do comparisons on Constants or Aggregates.
What I seem to need to be able to do is take the MaxDate from one source, turn it into a constant, that I can then compare against for each row in my data for the other connection. This is where I'm stuck...
Any help much appreciated