2 Replies Latest reply on Nov 29, 2016 8:06 PM by Mitch Sampson

    Sequential order logic calc with single date field

    Mitch Sampson

      Hi All,

       

      I’m trying to count instances where Date is not null for any row within a single Product and the Date increases in sequential order based on the Deal Type order (sample data attached where both conditions are met and product would be counted once).

       

      Only when there are no null Dates and the order of Deal Type (as found below) has order dates that increase sequentially do I count 1 for that Product.

       

      I’m having trouble figuring out the LOD calc needed for this one since I have to create aggregated views and therefore cannot use the lookup function (and can't use parameters as the dates will constantly be changing).

       

      Thanks for any help on this one as I have been racking my brain.

       

      Please let me know if any further info is needed.

        • 1. Re: Sequential order logic calc with single date field
          Jonathan Drummey

          I've got an LOD solution that works by counting dates & days instead of doing a row-by-row check for sequential values***, but I don't know enough about your data to be sure this would be accurate, for example do you have one record per product & deal type & date or multiple records? Assuming there is one record per product/deal type/date (which could be Null), the calc would be something like:

           

          {FIXED [Product] : COUNTD([Date]) = DATEDIFF('day',MIN([Date]),MAX([Date])) + 1 AND COUNTD([Date]) = SUM([Number of Records])}

           

          This gets the difference between total number of days (the DATEDIFF calc + 1) and the COUNTD() gets the number of non-null dates. If those are the same values then we know the days are sequential. If COUNTD([Date]) and SUM(Number of Records) are the same then we know there are no Null dates.

           

          If your data has a different grain then the calc would need to be adjusted.

           

          *** LOD expressions don't have functions that do inter-row comparison except under very limited circumstances by nesting LODs, to do inter-row comparison we typically need to use table calculations if we're going to do it entirely in Tableau, and table calculations can be nested so they can be used to do the further aggregations.

           

          Jonathan

          • 2. Re: Sequential order logic calc with single date field
            Mitch Sampson

            Hi Jonathan,

             

            Thank you for the quick reply and help.

             

            Your assumption that there is one record per product/deal type/date (which could be Null) is correct.

             

            I think two things are stopping this from still working.

             

            1. That the dates can be equal to or greater than (very sorry should have clarified that in the first post) within the sequential order.

            2. That the products need to have any of the 13 Deal Types, and as long as those existing deal types fall into a sequential order then it is valid.

             

            In the example below, Product 15 is missing Deal Type 9, but 8 and 10 are in sequential order (and none are null), so it is still valid.

             

            Please let me know if these aforementioned clarifications are something that can be overcome.

             

            Thanks again, as your first calc is definitely a step in the right direction