How to Sum and avoid duplicates

Question & Answer
, ,
1

Screen Shot 2023-01-26 at 3.02.30 AM
Screen Shot 2023-01-26 at 3.02.30 AM2688×774 172 KB

This is what my data looks like when I do a left join. You see there are duplicates because I am joining on only 2 columns while the base columns has more than 2 which is fine. However, in QS, I want to sum the traffic for each day. You can see it will duplicate if I sum, so doing an avg would work for days, but if I want to do months the avg wouldn’t work and the sum would be extremely high in this case. Is there a way in QS to avoid this duplication and only add it once when I do a month view and avoid using avg for a day view?

I also need to use the pivot table in QS as well. Any help would be appreciated!

2

@Phillip_Le - You perhaps need to utilize level aware aggregation calculation in this case. To get the sum accurate, you can try out something like below. Please let me know if this helps!

sum(avg({traffic}, [{metric_day}]))