Aggregation distinct count difference (Day vs Week vs Year)

[Rajmund]

Hey Team,
I’m working on project progress dashboard. I would like to present progress WoW, but it seems that Waterfall chart is counting ‘something extra’ when selecting different aggregation dates.
My ComponentsID may appear several times in a dataset, but I would like to count them only once (even if occurred several times). I used “distinct count” of ComponentsID, but anyway total sum of components is different based on aggregation level.
Aggregation DAY:

Aggregation WEEK:

Aggregation YEAR:

How to overcome this?
Thank you in advance for any hints

[Max]

This is because it will count the distinct ComponentId per week or per day.

So if you have an id of 1234 on week 33 and week 43 then it will count it twice on your progress because that is a unique component for that week. However, it will only count it once for the total because there is only one unique Component Id total.

The question would be how do you want to show that on your Progress Chart. For instance do you want to show only week 33 or week 43 or both (how it is now).

[Rajmund]

Hey Max, thank you for the answer.
I would like to show it only once, even if occurred in several weeks (or even years)

[Max]

Would you like to show it only the first time? So in this case we would only show it on week 33 and not on week 43.

[Rajmund]

Yes, I would like to count them and show only once (lets say first occurrence) on the waterfall. Because when customer “check in” a backup version with proper comment (our keyword) we consider this customer as “COMPLETED” regardless of how many check-ins they perform in the future

[Max]

Then I would look at taking a rank of ComponentId ordered by date & then add a filter to only have the rank be 1.

Rank - Amazon QuickSight

[Rajmund]

If someone would be interested in this topic. I’ve used what Max suggested.

rankComponentsID = rank([Timestamp ASC], [ComponentID], PRE_AGG)

Then filter to show only rankComponentsID equal to 1.
Thanks!