Edit: Is there maybe any other way to use secondary data source in Tableau because if i connect them in Tableau i get this Error Message:
"An error occurred while communicating with the data source.
Cannot blend the secondary data source because one or more fields use an unsupported aggregation."
Are you talking about Tableau Prep or Tableau Desktop? Any chance you can share a sample of your data?
i am talkin about Tableau Prep, the thing is in the Desktop version there is an option edit the relationship of multiple data sources, but in Prep i did not find something like that.
I shared a sample of my data in Desktop and in Prep to show exactly what is the problem.
Thank you for the clarification!
There are a couple of things to consider, but the basic approach I would take would be to create a calculation in the flow for each side of the join to truncate the dates to the desired level (month in this case).
Basically, you'd have
DATETRUNC('month', [Created at])
DATETRUNC('month', [Date of Payment])
However, the join will not be like blending in that you'll get extra records if more than one match is found and it will throw off all aggregations. So, you might also need to aggregate both sides of the flow to the month level (group by the new calculated fields + any other dimensions that define a unique record) prior to the join.
If you'd like clarification, I'd be happy to show you in a flow. The one you attached is just the flow; and, like an unpackaged workbook, it does not contain the data. However, if you export a packaged flow (.tflx) file, then we could all open it and see the data.
I was afraid the explanation would be difficult without a good example - so the packaged flow with data will hopefully help!
Here's the basic idea:
I've inserted a step in flow for the Order data to create a calculated field to truncate the Order Date to the month level. On the payment side I've added an aggregation to roll everything up to the month level. That will avoid duplicated records resulting from the join. That aggregation might not be needed if the data is already one record per month, but not knowing whether that would be true, I've included the step so you can see how it would be done if needed.
I've attached the flow so you can see it in action! Hope it helps!
Multiple data sources - JM.tflx 15.9 KB
now i got it how you did it. Thank you very much.
But now for each row is the value 1.500 like:
And if i want to calculate the CAC for example the Calculation would look like this:
and the result is: 1500
But it should be 250.
Do i maybe need now use a LOD expression to fixe my problem?
I got a way to solve this problem.
I created a SQL Statement to connect the data sources.
SELECT * FROM Table1 LEFT JOIN Table2 ON (YEAR(Table1.created_at) = YEAR(Table2.Date_of_Payment)) AND (MONTH(Table1.created_at) = (MONTH(Table2.Date_of_payment))
Thanks to everybody who tried to help me.