3 Replies Latest reply on Dec 20, 2013 1:03 AM by Dana Withers

    How to start window_sum() cacluation outside the view's date range

    Egor Ushakov
    Egor Ushakov Dec 18, 2013 11:30 PM

      I need to separate my customers by their activity level. Active customer means one that made an order within last three months. Inactive one is that who did NOT make any orders within last three month.

       

      I thought WINDOW_SUM([Unique Customers], -2, 0) (see workbook attached) would help me to get the job done. But as we know table calculations perform its job only over data already existing in a view. Thus window_sum() always starts from first value on  [Unique Customers] in my workbook and we have two left most window_sum() results incorrect from business point of view.

       

      Are there any ways to let window_sum() start its job from outside the view? Or any other approaches to substitute table calculation and overcome its limitations and get result needed?

        • 1. Re: How to start window_sum() cacluation outside the view's date range
          Dana Withers
          Dana Withers Dec 19, 2013 8:01 AM (in response to Egor Ushakov)

          I think it is possible but a bit ugly: you can make a duplicate data source (blended on customer name) that calculates the "active or not" and then use that for your colouring. However it only works when you create your graph on customer name level of detail, and you cannot group what is active and not based on that. So you end up with a bar chart consisting of stacked and mixed slices of two different colours. It does give you an overall idea of how much is active, but not a very precise one.

           

          Would it not be easier to include "active or not" as a field in your query?

           

          Hope that helps.

           

          Dana

          • 2. Re: Re: How to start window_sum() cacluation outside the view's date range
            Egor Ushakov
            Egor Ushakov Dec 20, 2013 12:30 AM (in response to Dana Withers)

            Thank you, Dana, for your reply!

             

            It seems the problem of defining "active"/"inactive" customers is a bit more complex than it appeared for me for the first sight. There are a lot of topics on the forum related to it. Here is one of those http://community.tableau.com/message/197406 . I understood the real depth of the problem after posting initial message and making calculation of "active" customers with formula like COUNTD( IIF( [OrderDate] >= DATEADD('month', -3, NOW()), [Customer Name], NULL)). At first I could not understand why later result is less then with WINDOW_SUM() calc. But then I realized that running calculation does not consider duplicating customers along months in the window. And I started to drill the forum to find solutions.

             

            But what I would like to clarify is what exactly formula you suggest to use to calculate "active or not" value. As I wrote above one can differentiate customers by [OrderDate] but only using NOW() as reference point for calculation. But if we'd like to use master date dimension as primary data source (see discussion here http://community.tableau.com/thread/136227 to get an idea) to leverage date calculation we'll face with "All fields must be aggregate or constant when using table calculation function or fields from multiple data sources" issue.

             

            Could you provide a formula you mentioned for "active or not" field?

            • 3. Re: Re: Re: How to start window_sum() cacluation outside the view's date range
              Dana Withers
              Dana Withers Dec 20, 2013 1:03 AM (in response to Egor Ushakov)

              Hi Egor,

               

              I've attached the workbook I played with in answering your question. As I mentioned, I may have the formula and I think it is all correct, but in blending it is not exactly looking very clear.

               

              Hope it helps though!

               

              Dana