Let's say I have a table called message_viewed, and the table looks like this
date msg_id msg_unique_user flow_unique_user campaign_unique_user
1/1/2010 1 100 90 80
1/1/2010 2 80 90 80
1/2/2010 1 80 70 60
1/2/2010 2 90 70 60
If want the flow_unique_user and campaign_unique_user to be semi-additive, that is I want them summed over only the date, and not the msg_id, what is the best way to do it?
For example, for 1/1/2010, if I select msg_id 1 and 2, the flow_unique_user is 90 (not 180), and campaign_unique_user is 80(not 160).
If I pick, 1/1 and 1/2 and msg_id, 1 and 2, the flow_unique_user would be 160 (320), and campaign_unique_user would be 140 (not 280).
Your Semi-Additive field should only be associated with the date in a diferent talbe in your datacloud. Then it won´t be duplicated when aggregatin with sum. If you bring duplicated values in the "fact" table, it must be treated in the chart expression, like dividing by count(distinct date)
.png "fernandotoledo"){kind=avatar}
