Explore Courses Blog Tutorials Interview Questions

Explore Tech Questions and Answers

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

0 votes
by (17.6k points)

I can use either SQL Server or Vertica as the DB and Tableau as the reporting tool. A solution in any of these mediums would be helpful.

DATA RESOURCES: I have a table (userActivity) with 100 records and a structure of User, StartDate, EndDate

NEED: I am interested in preparing reports by day and month that show "total active days", meaning if User1 has a range of '20180101' to '20180331', they will contribute one day for each day in Jan, Feb, and Mar OR 31, 28 and 31 days if aggregated by month.

GOAL: I will ultimately be aggregating the total active days of all users as the output to achieve a single total for each day/month.

This report will span to perpetuity, so I would prefer solutions that don't hard code CASE/IF-THEN statements by day/month.


1 Answer

0 votes
by (47.2k points)
  • While recursive CTEs are a good candidate for this scenario, it can be handled with tableau alone. Assuming you have this data, here are the steps required to produce the view.

enter image description here

  • Create a reference sheet that has all the days expected. Even if you need to cover 25 years from 01/01/2018 to 01/01/2043, that is still less than 10k rows.

enter image description here

  • You need two columns with exact same date as Tableau does not allow multiple join conditions on the same column.

  • Create an inner join between reference calendar and data using the following criteria. enter image description here

Build the view
enter image description here

Related questions

Browse Categories