Hello,
I try to create a straight table object with data from different fact types.
The dimensions are a mixture of data out of the user and activities table (two different fact types).
The values are calculated visits from a third table (fact type visits).
In my data model activities and visits are stored in the fact table (concatenated), the users are hanging outside linked by the user ID to the fact table.
The result should be a unique line per day with the user information, activity and the calculated visits for that day.
I tried to sum the visits in different ways. I displayed both formulas side by side but in general I need only one calculation per user.
When I use "sum (visits)" I get the visits only calculated on the second line per day for the fact type visits.
When I use "Sum (aggr( NODISTINCT sum( visits ), name, date)) " I get the correct number of visits calculated per day in the above line and a 2nd line for the fact type visits with a wrong sum like in my example.
Is there a way to hide the second line in combination with the aggr function or is this maybe a problem of my data model?
| name | date | weekday | actvitiy | sum visits | sum aggr visitis |
| user1 | 01.12.2017 | Fr | visit | 0 | 8 |
| user1 | 01.12.2017 | Fr | | 8 | 104 |
| user1 | 04.12.2017 | Mo | visit | 0 | 9 |
| user1 | 04.12.2017 | Mo | | 9 | 108 |
| user1 | 05.12.2017 | Tu | double visit | 0 | 3 |
| user1 | 05.12.2017 | Tu | | 3 | 30 |
| user1 | 06.12.2017 | We | visit | 0 | 9 |
| user1 | 06.12.2017 | We | | 9 | 108 |
| user1 | 07.12.2017 | Thr | visit | 0 | 10 |
| user1 | 07.12.2017 | Thr | | 10 | 130 |
| user1 | 08.12.2017 | Fr | visit | 0 | 9 |
| user1 | 08.12.2017 | Fr | | 9 | 108 |
| user1 | 11.12.2017 | Mo | visit | 0 | 11 |
| user1 | 11.12.2017 | Mo | | 11 | 154 |
| user1 | 12.12.2017 | Tu | visit | 0 | 11 |
| user1 | 12.12.2017 | Tu | | 11 | 154 |
| user1 | 13.12.2017 | We | visit | 0 | 8 |
| user1 | 13.12.2017 | We | | 8 | 88 |
I would be glad about any suggestions!
Many thanks in advance and best regards,
Claus
