Hi All - New to Tableau here (first post).
I'm trying to calculate the number of customers that registered for an event at a certain time during a registration period, and then plot those counts. Some of these events take place each year, however the registration periods as well as the date of the event are never the same from year to year. As such, the standardization we use to compare year-over-year are the number of days the event's registration has been open, and the number of days until the event which is calculated from each customer's registration date for the particular event in a particular year. Each event in each year has a unique Event ID, and event names are the same across years.
I'm working from 2 data sources. The first is a list of the events by ID with information on the event (name, registration, date, etc.), and the second is the registrations by customer ID, event ID, and a registration date/time stamp. Currently I have these data sources blended on Event ID (the only common field). What I can't figure out is how to calculate the number of days since registration opened (and the number of days until the event) a customer registered using the registration date/time and the corresponding dates from the first data source. I then need to create KPIs for difference and growth in registration for the current year compared to the previous after the same number of days open/days to event, but I'm struggling with just getting the latter calculated in the first place.
The attached packaged workbook contains sample data, and the 'cf.DaysOpenReg' field calculation has all of the calculations I've tried thus far. The 'GraphWithDatesDaily[Cumulative]' sheets have registrations aggregated by dates, and almost what I'm looking for in the viz, but the lines would be on top of each other when using the days open/to event calculations instead of date. Finally full disclosure, I'm a long-time R user and I'm still trying to wrap my head around Tableau's need for aggregation functions in calculations as well as LOD calculations. Any help would be greatly appreciated.
Thanks in advance!
I've managed to create the graphs via a workaround of sorts by creating a new data source that joins the event information to the event registrations ('NewSource' sheets in the attached packaged workbook). It's also entirely possible that this isn't a workaround and is how this is supposed to be done in Tableau. I would still be interested to know whether or not the calculation is possible with the blended data source.
HelpWorkbook.twbx 2.0 MB