-
1. Re: Yearly Average by Product
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 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?
-
tableau example.twbx 614.3 KB
-
-
3. Re: Yearly Average by Product
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 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 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