1 Reply Latest reply on Jan 12, 2017 7:40 AM by Stephen Rizzo

    "Number of Stores with Zero sales till date", not on a particular day - Line chart

    Saharsh Maheshwari
    Saharsh Maheshwari Jan 12, 2017 6:03 AM

      Hi,

      I have to plot the 'Count of stores with Zero sales' on a trend line over time.
      I have plotted it Tableau in the attached file, where Date is in the Column Shelf, CNT(Stores) is on Row Shelf and filter is kept at Total Sales = 0, but this is not what I am looking for.
      If a store has made a sale on a particular day, it should not be counted in Zero sales stores from next day onwards.

      The graph should show "Number of Stores with Zero sales till date", not on a particular day.
      Data and the Chart I want to make on tableau are in the excel file.

       

      Thanks in advance
      Saharsh Maheshwari

        • 1. Re: "Number of Stores with Zero sales till date", not on a particular day - Line chart
          Stephen Rizzo
          Stephen Rizzo Jan 12, 2017 7:40 AM (in response to Saharsh Maheshwari)

          You can do this with the following two calculated fields:

           

          [Running Max] : RUNNING_MAX(SUM(IF [Total Sales] > 0 THEN 1 ELSE 0 END))

           

          The IF statement covers for the case that you don't only have 1s in your [Total Sales] field. If you pull [Store] into your viz, this field returns zero if the store had zero sales up until the displayed date, 1 otherwise. The next step is to sum these indicators by day, then subtract that from the total number of stores:

           

          [Number of Stores with Zero Sales] : ATTR({FIXED : COUNTD([Store])}) - WINDOW_SUM([Running Max])

           

          In order for this to work, you will need [Store] in your Detail shelf, then compute [Running Max] using [Date] and [Number of Stores with Zero Sales] using [Store].