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

    Sales Target Reached for 10 days

    Mark Smith



      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



        • 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.