2 Replies Latest reply on Nov 24, 2016 6:39 AM by Mark Smith

    Sales Target Reached for 10 days

    Mark Smith

      Hi

       

      I'm looking to create a flag which is displayed when certain criteria are true

       

      The sales were 90% of target for at least 10 days in a month AND

      this occurred in a least 3 months of the financial year

       

      I can get a flag when it is true for any month

       

      if WINDOW_SUM([Sales Target Reached],FIRST(),LAST())>=5 then 1 else

      0 end

       

      where Sales Target Reached

       

      if SUM(Sales)/SUM(Target)>=.90 then countd([Order Date]) end

       

      but having difficulty counting the number of months this is true

       

      Thanks in advance

       

      Mark

        • 1. Re: Sales Target Reached for 10 days
          Andrew Watson

          This can be done using LOD calculations (as long as you're using Tableau 9+). My approach feels slightly convoluted so there could be a neater way to do it...however the most important thing is that it works.

           

          First for each order day we want to return a flag showing whether it's greater than 90% of target:

           

          { FIXED [Order Date]:IF SUM([Sales])/SUM([Target]) >= 0.9 THEN 1 END}

           

          Summing that field will give the total days greater than 90% of target. However you want to identify months with 10 or more days greater than 90%.

           

          This will sum the number of days greater than 90% for each month (with Calculation1 being the calculated field created above):

           

          { FIXED MONTH([Order Date]),YEAR([Order Date]):IF SUM([Calculation1]) > 10 THEN 1 END}

           

          That will return a 1 for those months with more than 10 days above 90%. You can sum that to count the relevant months in a time period.

          • 2. Re: Sales Target Reached for 10 days
            Mark Smith

            Many thanks Andrew, works a treat - I did have to replace YEAR and MONTH with a custom date to get to work but all good.