Hello,
I have 2 data sources - 3 datasets from Bigquery and Excel file. I have 4 dimensions.
For example, 'a' in which there are values for each day and 'b' in which also there are values, etc. Looks like this
a (excel source)
---
userid date
aa1 2018-06-27 12:16:52.000
bb1 2018-06-27 11:11:52.000
aa1 2018-06-26 09:16:52.000
bb1 2018-06-26 18:16:52.000
cc1 2018-06-25 18:16:52.000
b (Bigquery source)
---
userid date
aa1 2018-06-27 19:16:52.000
bb1 2018-06-27 01:11:52.000
aa1 2018-06-26 03:16:52.000
cc1 2018-06-27 18:16:52.000
bb1 2018-06-26 15:16:52.000
c (Bigquery source)
---
userid date
aa1 2018-06-27 19:16:52.000
bb1 2018-06-27 01:11:52.000
aa1 2018-06-26 03:16:52.000
cc1 2018-06-27 18:16:52.000
bb1 2018-06-26 15:16:52.000
d (Bigquery source)
---
userid date
aa1 2018-06-27 19:16:52.000
bb1 2018-06-27 01:11:52.000
aa1 2018-06-26 03:16:52.000
cc1 2018-06-27 18:16:52.000
bb1 2018-06-26 15:16:52.000
At first I made JOIN tables by userid (like this) http://joxi.net/RmzoyVkS0vLd92
I have to create line graphic to show user dynamics per day - I made count distinct users and convert it to measure and that was okay. After that I had 4 measures for each dataset and discover changes of distinct amount of users per day in each dataset. Further I have to calculate coefficients between a/b distinct users, a/c distinct users and a/d distinct users in general. That was okay - cause I've created new measures and divide one measure to another. Like this COUNTD([userId (a)]) / COUNTD([userId (b)])
But after that i need to see change of coefficients by days. I can't solve this problem because none of two dates ('date' from 'a' and 'date' from 'b') does not working. http://joxi.ru/a2XnNGQC1Qk5dr
Did somebody have similar difficulties in solving problem merge dates from different sources for aggregate measures??
Hi Volodymyr,
Why not just UNION all your tables into one datasource?
The tables are of the same structure (at least for this analysis),
so there would be just one [date] column in the final datasource,
and your COUNTD() aggregations (and their Ratio calcs) would work seamlessly.
And you'd be able to distinguish the original tables by the [Table Name] column.
To UNION tables from different Connections (you've got Excel and BQ),
you may want to apply the 'Scaffold' technique by null:
Yours,
Yuri
