2 Replies Latest reply on Jun 27, 2018 8:21 AM by Phil R

# 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!

• ###### 1. Re: How to have a FIXED calculation consider a selected filter value?

Hi Phil,

Here it Is

ALWAYS ADD FILTERS TO CONTEXT WITH FIXED LOD

These are the Calcs you need in your Situation:

For 25 Weeks

For 18 Weeks

FOR 17

FOR All Subs and all Weeks

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread

• ###### 2. Re: How to have a FIXED calculation consider a selected filter value?

Hi Deepak, I am trying to get a distinct subscriber count by the total number of events they have had based upon the [Weeks between subscription and event] filter - the sum of subscriber counts should not exceed the number of unique subscriber ids in the dataset. In this case, that is 46.

So for example:

- When the user selects 2 weeks, I need this subscriber to be counted only once under 3, because they have had 3 events as of 2 weeks between subscription and events.

- When the user selects 8 weeks, then the subscriber should be counted only once under 5, because they have had 5 events as of week 8.

 subscriber id subscription date event date event date - subscription date weeks between event date and subscription date event count 7920 1/30/2017 2/6/2017 7 1 1 7920 1/30/2017 2/10/2017 11 1 1 7920 1/30/2017 2/15/2017 16 2 1 7920 1/30/2017 3/27/2017 56 8 1 7920 1/30/2017 4/1/2017 61 8 1 7920 1/30/2017 4/3/2017 63 9 1 7920 1/30/2017 4/27/2017 87 12 1 7920 1/30/2017 5/12/2017 102 14 1 7920 1/30/2017 7/31/2017 182 26 1

Please let me know if I can clarify anything else. Thanks!