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?

    Phil R

      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 idsubscription dateevent dateevent date minus subscription dateweeks between event date and subscription dateevent count
      10381/14/20171/23/2017911
      10381/27/20171311
      10381/30/20171621
      10382/5/20172231
      10382/15/20173241
      10382/24/20174151
      10383/19/20176491

       

      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 idsubscription dateevent dateevent date - subscription dateweeks between event date and subscription dateevent count
      16441/11/20175/15/2017124171
      16445/16/2017125171
      16445/23/2017132181
      16447/5/2017175251
      16447/20/2017190271
      16448/15/2017216301
      164410/12/2017274391
      164410/26/2017288411

       

      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?
          Deepak Rai

          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?
            Phil R

            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 idsubscription dateevent dateevent date - subscription dateweeks between event date and subscription dateevent count
            79201/30/20172/6/2017711
            79201/30/20172/10/20171111
            79201/30/20172/15/20171621
            79201/30/20173/27/20175681
            79201/30/20174/1/20176181
            79201/30/20174/3/20176391
            79201/30/20174/27/201787121
            79201/30/20175/12/2017102141
            79201/30/20177/31/2017182261

             

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