J
JaiWoods
Guest
Hello,
I have some data I'm trying to find a way to present in a dynamic table but it has to meet a date condition which makes it a bit tricky. I will try my best to explain with an example:
So what I'm trying to do is setup a dashboard where I can select an Event from an Event List and it would populate a list of all customers and 5 columns for the total number of times they participated in each activity (Activities A-E) ONLY if the activity date is on or after the Event Start Date.
It gets a bit trickier; All the tables don't have relationships with each other because I'm using PowerPivot and because the list of Activities has duplicates it prevents the relationship, so I had to create 2 unique list tables to relate Table 2 to all the Activity tables based on customer id and Table 1 to Table 3 because it is the only other list with the Event Name in the data.
Also, it wouldn't be a count of the occurrences of the customer id in each table because there is a column with a value in it I need to sum for each row if that row meets the criteria above.
I have 7 tables:
Table 1: A list of all events YTD & Event Start Date (unique list)
Table 2: A list of all customer ids that had activity YTD (unique list)
Table 3: (Activity A): A list of all customers who participated in Activity A for an event YTD (matches to Table 1 as a one to many relationship) and the date they did the activity This table has duplicate event names & customer ids because a customer can participate in Activity A multiple times so the dates will be different for each activity record)
Table 4: (Activity B): A list of all customers who participated in Activity B YTD (matches to Table 2 as a one to many relationship) and the date they did the activity (this has NO event names tied to it, only customer ids and the date the activity occurred) This table has duplicate customer ids because a customer can participate in Activity B multiple times so the dates will be different for each activity record)
Table 5: (Activity C): A list of all customers who participated in Activity C YTD (matches to Table 2 as a one to many relationship) and the date they did the activity (this has NO event names tied to it, only customer ids and the date the activity occurred) This table has duplicate customer ids because a customer can participate in Activity C multiple times so the dates will be different for each activity record)
Table 6: (Activity D): A list of all customers who participated in Activity D YTD (matches to Table 2 as a one to many relationship) and the date they did the activity (this has NO event names tied to it, only customer ids and the date the activity occurred) This table has duplicate customer ids because a customer can participate in Activity D multiple times so the dates will be different for each activity record)
Table 7: (Activity E): A list of all customers who participated in Activity E YTD (matches to Table 2 as a one to many relationship) and the date they did the activity (this has NO event names tied to it, only customer ids and the date the activity occurred) This table has duplicate customer ids because a customer can participate in Activity E multiple times so the dates will be different for each activity record)
I have some data I'm trying to find a way to present in a dynamic table but it has to meet a date condition which makes it a bit tricky. I will try my best to explain with an example:
So what I'm trying to do is setup a dashboard where I can select an Event from an Event List and it would populate a list of all customers and 5 columns for the total number of times they participated in each activity (Activities A-E) ONLY if the activity date is on or after the Event Start Date.
It gets a bit trickier; All the tables don't have relationships with each other because I'm using PowerPivot and because the list of Activities has duplicates it prevents the relationship, so I had to create 2 unique list tables to relate Table 2 to all the Activity tables based on customer id and Table 1 to Table 3 because it is the only other list with the Event Name in the data.
Also, it wouldn't be a count of the occurrences of the customer id in each table because there is a column with a value in it I need to sum for each row if that row meets the criteria above.
I have 7 tables:
Table 1: A list of all events YTD & Event Start Date (unique list)
Table 2: A list of all customer ids that had activity YTD (unique list)
Table 3: (Activity A): A list of all customers who participated in Activity A for an event YTD (matches to Table 1 as a one to many relationship) and the date they did the activity This table has duplicate event names & customer ids because a customer can participate in Activity A multiple times so the dates will be different for each activity record)
Table 4: (Activity B): A list of all customers who participated in Activity B YTD (matches to Table 2 as a one to many relationship) and the date they did the activity (this has NO event names tied to it, only customer ids and the date the activity occurred) This table has duplicate customer ids because a customer can participate in Activity B multiple times so the dates will be different for each activity record)
Table 5: (Activity C): A list of all customers who participated in Activity C YTD (matches to Table 2 as a one to many relationship) and the date they did the activity (this has NO event names tied to it, only customer ids and the date the activity occurred) This table has duplicate customer ids because a customer can participate in Activity C multiple times so the dates will be different for each activity record)
Table 6: (Activity D): A list of all customers who participated in Activity D YTD (matches to Table 2 as a one to many relationship) and the date they did the activity (this has NO event names tied to it, only customer ids and the date the activity occurred) This table has duplicate customer ids because a customer can participate in Activity D multiple times so the dates will be different for each activity record)
Table 7: (Activity E): A list of all customers who participated in Activity E YTD (matches to Table 2 as a one to many relationship) and the date they did the activity (this has NO event names tied to it, only customer ids and the date the activity occurred) This table has duplicate customer ids because a customer can participate in Activity E multiple times so the dates will be different for each activity record)