Hi Quicksights,
We are trying to subtract once runningcountdate from another runnign coutn date to calculate actives user over a longer time period. We are new to quicksights and are sure there is a solution for this but have not been able to find it so far in the community pages.
We are able to create a runningcount of active users over time based on created_at and deactivated_at date values. But when trying to show the difference between the running counts over time we run into an error.
To further specify, a general line in this table contains:
userid: UUID
createdAt: date the user was created
deactivatedAt: date the user was removed.
The challenge is that we want to measure all users which were active over time on specific dates.
We define a user as active if the date is on or after the createdAt date and before the deactivatedAt date (or deactivatedAt is null).
In PowerBI this was doable by linking a datetable to the model, and then we count the users per date which match the filters. We’re at a loss how to perform something similar in Quick Sights.
Hello Rody / Franc,
First of all welcome to the QS Community.
If possible could share some sample/dummy data with expected outcome. I think Community members can surely find some solutions.
Thanks for the info.
Cheers,
Deep
Hey Deep,
Thanks for the response. Some sample dummy data:
| UUID | createdAt | deactivatedAt |
|---|---|---|
| 2313-uudi-213 | 1-1-2023 | 1-2-2023 |
| 2313-uudi-214 | 1-1-2023 | 1-2-2023 |
| 2313-uudi-215 | 1-1-2023 | 1-2-2023 |
| 2313-uudi-216 | 1-1-2023 | 1-2-2023 |
| 2313-uudi-217 | 1-1-2023 | 1-2-2023 |
| 2313-uudi-218 | 1-1-2023 | 1-2-2023 |
| 2313-uudi-219 | 1-1-2023 | 1-2-2023 |
| 2313-uudi-220 | 1-1-2023 | 1-2-2023 |
| 2313-uudi-221 | 1-1-2023 | 1-3-2023 |
| 2313-uudi-222 | 1-1-2023 | 1-3-2023 |
| 2313-uudi-223 | 1-2-2023 | 1-3-2023 |
| 2313-uudi-224 | 1-2-2023 | 1-3-2023 |
| 2313-uudi-225 | 1-2-2023 | 1-3-2023 |
| 2313-uudi-226 | 1-2-2023 | 1-3-2023 |
| 2313-uudi-227 | 1-2-2023 | 1-3-2023 |
| 2313-uudi-228 | 1-2-2023 | 1-3-2023 |
| 2313-uudi-229 | 1-2-2023 | 1-4-2023 |
| 2313-uudi-230 | 1-2-2023 | 1-4-2023 |
| 2313-uudi-231 | 1-2-2023 | 1-4-2023 |
| 2313-uudi-232 | 1-2-2023 | 1-4-2023 |
| 2313-uudi-233 | 1-2-2023 | 1-4-2023 |
| 2313-uudi-234 | 1-2-2023 | 1-4-2023 |
| 2313-uudi-235 | 1-2-2023 | 1-4-2023 |
| 2313-uudi-236 | 1-2-2023 | 1-4-2023 |
| 2313-uudi-237 | 1-2-2023 | 1-5-2023 |
| 2313-uudi-238 | 1-2-2023 | 1-5-2023 |
| 2313-uudi-239 | 1-2-2023 | Null |
| 2313-uudi-240 | 1-2-2023 | Null |
| 2313-uudi-241 | 1-2-2023 | Null |
Where I’d expect an outcome such as this: (dates are dd-mm-yyyy)
| Date | active users |
|---|---|
| 2-1-2023 | 10 |
| 2-2-2023 | 21 |
| 2-3-2023 | 13 |
| 2-4-2023 | 5 |
| 2-5-2023 | 3 |
| 2-6-2023 | 3 |
HEllo Frank,
Are you using RDS as backend? If you can have Calendar date as shown is expected date in the source it would be easier to achieve your desired or you may also use custom sql to union two date (createdAt and deactivatedAt and make it as date. i guess this way you make easy calculation to count occurrence of uuid as active users with a date month.
hope this gives some direction.
Cheers,
Deep
