there are two tables that I have 1 for product usage and one for the user, I joined it via union for me to also see who are those users that did not use the product, but once I applied filters for the dates e.g. I want to view all users (used or not) of the product, the inactive users are not included in the list. how can I also view those that has 0 usage?
Hi @sharmagneo, I assume you used cross join to reserve all the combination of products and users? what key are you using when join? also, which table has the dates?
I use the full outer join in quicksight, the key I used is the user id, both tables have dates for different attributes, for the usage it has date for when the user has used the products, which i used for my filters, then there is also date for the user table, the date is for when was the user subscribe or created in the system.
Hello @sharmagneo , I think in your case you need a LEFT join (assuming user table is the one on the left).
Reason for this is because you want to include in your dataset all the users that used your products (active) + the users that didn’t used the products (inactive). In other words, all the user ids that are present in both tables (user and product) plus the user ids that are not on the product usage (inactive)
Once you have changed the join type you will get a table with rows representing these two groups:
- Active users (rows that contain all the joined columns with values)
- Inactive users (rows that values only on the user columns and have product usage columns set to null)
With the situation explained above, you just need to create a calculated field to add a column (e.g isActiveUser) to return 0 if it is not or 1 if its active. The code for that calculated field could be similar to this:
ifelse(isNull({product_usage_field})
Functions used are ifelse and isNull
Hope it helps, please mark this solution as solved if that’s the case also to help other members of the community., otherwise let us know.
Happy dashboarding!