How to have a FIXED calculation consider a selected filter value?
Hi all, I am new to Tableau and will do my best to describe my problem. I need to create a simple bar chart viz that counts subscribers by the total number of events that occur for each subscriber between two dates. The tricky part is that I need the subscriber count in the viz to reflect the total number of events based upon the filter selection.
Each subscriber has a static [Subscription Date], and one or more [Event Date]s:
| subscriber id | subscription date | event date | event date minus subscription date | weeks between event date and subscription date | event count |
| 1038 | 1/14/2017 | 1/23/2017 | 9 | 1 | 1 |
| 1038 | 1/27/2017 | 13 | 1 | 1 | |
| 1038 | 1/30/2017 | 16 | 2 | 1 | |
| 1038 | 2/5/2017 | 22 | 3 | 1 | |
| 1038 | 2/15/2017 | 32 | 4 | 1 | |
| 1038 | 2/24/2017 | 41 | 5 | 1 | |
| 1038 | 3/19/2017 | 64 | 9 | 1 |
To create the viz, I created two fields:
clc_Fixed Event Count = {FIXED [Subscriber Id] : SUM([Event Count])}
clc_Subscriber Count = COUNTD([Subscriber Id])
Problem: the clc_Fixed Event Count field is fixed at the Subscriber Id level, I need it to be fixed at the Subscriber Id level AND calculate ONLY for the number of weeks selected in the filter.
For example, this subscriber will have their total 8 events reported on the viz once the filter is >= 17. I need it to be counted as 2 at week 17, 3 at week 18, 4 at week 25, etc. etc.
| subscriber id | subscription date | event date | event date - subscription date | weeks between event date and subscription date | event count |
| 1644 | 1/11/2017 | 5/15/2017 | 124 | 17 | 1 |
| 1644 | 5/16/2017 | 125 | 17 | 1 | |
| 1644 | 5/23/2017 | 132 | 18 | 1 | |
| 1644 | 7/5/2017 | 175 | 25 | 1 | |
| 1644 | 7/20/2017 | 190 | 27 | 1 | |
| 1644 | 8/15/2017 | 216 | 30 | 1 | |
| 1644 | 10/12/2017 | 274 | 39 | 1 | |
| 1644 | 10/26/2017 | 288 | 41 | 1 |
Does that make sense? Please let me know if I can clarify anything. Thanks!
Jun 26, 2018 8:18 PM
