5 Replies Latest reply on Oct 14, 2018 4:28 PM by Jonathan Brough

    Yearly Average by Product

    Alex Martino
    Alex Martino Oct 12, 2018 11:01 PM

      How do I get a yearly daily average to ignore months where the average is 0 for a product (there are no sales during that month)

       

      AVG({FIXED [Category], month([Date]),year([Date]):SUM([Sales Volume])}) is current formula but it is showing a number that is way too low for products that got released in the middle/end of the period.

       

      It's been super difficult to figure this out:

       

      I want the monthly daily average by product FIRST. But there has to be a way to identify new products (first day in period where value >0) and then for that month the daily average is not divided by total days in the month but rather days from start to end of that first month.

       

      Then for the yearly daily average, I want the combined monthly daily average starting from the first date of activity (most products will be active at beginning of year).

       

      Please help!!!!

      • 124 Views
      • Tags:
        • 1. Re: Yearly Average by Product
          Jonathan Brough
          Jonathan Brough Oct 13, 2018 1:11 AM (in response to Alex Martino)

          Hi,

          Is it too crude to use the count of the days that sales have occurred on as your denominator ?

          Do you get sales most days, and do you get them over weekends. Would you want to count weekend days when there are not normally sales on the weekend days ?

          Thanks,

          Jonathan

          • 2. Re: Yearly Average by Product
            Alex Martino
            Alex Martino Oct 13, 2018 2:04 AM (in response to Jonathan Brough)

            Hi Jonathan Brough yeah it's too crude unfortunately The reason is that I want those days to be in the denominator AFTER a product has started (which very rarely occurs for some products).

             

            Here is a data set.

             

            1) In the "last 30 days tab" I want to take the daily average for this entire period. But for the newer products it should only divide by the numbers of days from the first day that is >0. For example, for slouchy product, the denominator should be 17 days.

             

            2) If you look at the third tab, "Sales by Product Month Average", the monthly averages need to account for what I explained in section 1 above. If you look at slouchy at the bottom for September, the average is understated.

             

            3) Finally, I would like to then create the TOTAL monthly average for the entire period of dates. HOWEVER, for slouchy (and a few other products) the problem is the same above AND ALSO it is accounting for months/days prior to the launch of the product so the overall monthly average is even less.

             

            How can I account for this? 

            • 3. Re: Yearly Average by Product
              Jonathan Brough
              Jonathan Brough Oct 13, 2018 2:39 AM (in response to Alex Martino)

              Understood. But a count of the days will only yield those days when products are sold, and therefore after the product was first sold.

              Not got time to think through this more advanced question just  now I am afraid.

              Jonathan

              • 4. Re: Yearly Average by Product
                Alex Martino
                Alex Martino Oct 13, 2018 2:53 AM (in response to Alex Martino)

                Yeah, but the key is the "first sold" piece after that, even if there is a 0 that day should be counted...just not before.

                 

                It seems like no one has time for this one haha I've been trying to get someone to answer for a week.

                • 5. Re: Yearly Average by Product
                  Jonathan Brough
                  Jonathan Brough Oct 14, 2018 4:28 PM (in response to Alex Martino)

                  Hi again,

                  I suspect my starting to answer has stopped others evaluating the question, so you may want to start another thread.

                  In the meantime, the following LOD will get you the starting date for each Category:

                   

                  min({FIXED [Category],[Date] : Min(

                  IF [Sales Volume] > 0 THEN [Date] END

                  )})

                   

                  The following Knowledge Base article should also shed light on how to get a date duration for the number of days from first to last sale (though you would need to include the IF statement in your LOD, as above)

                   

                  https://kb.tableau.com/articles/howto/additional-cohort-analysis-example

                   

                  Hope this moves you a bit closer.

                  Thanks,

                  Jonathan