2 Replies Latest reply on Mar 2, 2016 6:11 PM by Scott Bernal

    How to count something daily for last 30 days and NOT aggregate?

    Scott Bernal

      Hello everyone! I'm fairly new to Tableau, and I'm trying to create a KPI where I am counting late shipments. What I have done is create a calculated field that shows either 1 if late, or 0 if not (taking into account a lag):

      IF [SHIP_DT]>([REQD_DT]+.75) then 1 ELSE 0 END

      I have the above field in the Rows shelf. For the columns shelf, I want to have dates for the last 30 days (or whatever) to today, so I would have a bar for each day. For the bar's underlying data, I want it to show me a sum of how many shipments were late... But here's where it gets tricky.

      Example:

      Shipment Required date is 1/1

      Shipment Shipped date is 1/3

      So, the shipment is late 2 days, on 1/2 and 1/3. I want the columns for the 2nd and 3rd to include the count of 1 for however many shipments were late those days. I DO NOT want the column to sum the days late and just show that to me on the date the item shipped - that would be too easy!

      The biggest issue up to this point is trying to get the columns shelf to show me the last 30 days without being tied to any of my data. Maybe if I could figure that part out the other issue of aggregating the sum of lates would go away. Although I'm thinking I need a formula that dictates, "If >= REQD_DT and <=SHIP_DT then show up on every column between those two dates" and I don't quite know how to do that.

      Suggestions would be greatly appreciated!

      Best regards,

      Scott

        • 1. Re: How to count something daily for last 30 days and NOT aggregate?
          Michael Miller

          To show all the days in the month regardless of there being data or not, try right clicking on your date on the columns shelf and changing the field to continuous rather than discrete. Still trying to understand the other part of the question.

          • 2. Re: How to count something daily for last 30 days and NOT aggregate?
            Scott Bernal

            Thank you Michael. I changed the date to Continuous, but I still need to work on the other part. I'll try to explain what I'm looking for... basically I want to count how many shipments were late. So if the Ship Date is > Required date, then for each day that passed between the Required Date (the date it was supposed to ship) and the (actual) Ship Date, that shipment counts as 1. So if the product was supposed to ship Monday, but didn't ship until Wednesday, you would see a count of 1 on Monday, and 1 on Tues, and 1 on Wed. And I would like to see the last 30 days history.

            My calculated field "LateOrNot" shows the 1's when the shipments were late (and 0's if not), but I can't get that field to sum for each day - Tableau wants to sum them all on the REQD_Dt.

             

            Thank you again!

            Scott