13 Replies Latest reply on Sep 25, 2018 1:08 AM by Mahfooj Khan

# How to get last 30 day average

Hello. How do I get last 30 day average for asins USING THE MOST RECENT DATE IN MY DATA SET (as it is updated)

I have a category: Polo Shirts and then I have different size/color combinations (asins)

How can I calculate the daily average for the last 30 days. I think that whenever there are 0 sales for a date, the date just doesn't show up, so the formula would have to take this into account.

Thanks!!

• ###### 1. Re: How to get last 30 day average

Hi Alex,

You can refer below articles:

• ###### 2. Re: How to get last 30 day average

Could you mind attach a sample workbook based on that it will be easy for us to crack the logic

BR,

NB

• ###### 3. Re: How to get last 30 day average

Hi,

Try this,

Create a calculated field to fetch the last 30 days

Put this field in filters shelf and select "True"

Create another calculated field to get the AVG sales of last 30 days

//Kindly include the level of details in FIXED{} as per your requirement. Like include Category,Size,Color etc

{FIXED :AVG([Sales])}

Let us know if this help.

Mahfooj

• ###### 4. Re: How to get last 30 day average

Hi Mahfooj Khan thanks. When I add the average calculation it returns 1 for all my subcategories. Added screenshot.

• ###### 5. Re: How to get last 30 day average

Put your filters on context and check.

• ###### 6. Re: How to get last 30 day average

On context? What does that mean? And how does that work?

• ###### 7. Re: How to get last 30 day average

Right Click on Filter --> Add to Context

BR,

NB

• ###### 8. Re: How to get last 30 day average

Whenever we use FIXED{} LOD expression in our calculation and if we have put any dimension on filters then we have to put it on context so that our dimension filter should apply before our FIXED{} expression.

Hope this will help

Mahfooj

• ###### 9. Re: How to get last 30 day average

None of this is working. It all returns an average of 1.

I created a snippet of my data. In this case, it's just 4 or 5 days of data, but the format is the same as my original data set.

Again, I'm trying to get the DAILY AVERAGE of orders (in my data set it will be by dimension but in this case it's just overall.

• ###### 10. Re: How to get last 30 day average

Hi,

Let us know if this work,

Include your other dimensions in FIXED{} expression like {FIXED [Category],[Color],[Size],[Date]......

Mahfooj

2 of 2 people found this helpful
• ###### 11. Re: How to get last 30 day average

Yes that works. Can you explain why that works and why the other formula does not? Also, how can I make this dynamic? For example, if I put category #1, it's wrong, or #1 and #2 it's wrong. It seems dumb if I to have a different calculation each time I want to change the visibility of category/subcategor etc

• ###### 12. Re: How to get last 30 day average

That formula actually doesn't work when I add categories. See screenshot. That average is not the sum divided by 30 days. Not sure why it doesn't work. Maybe because some of those categories don't have data for each day, so it's just dividing by the days that are present? I want it to divide by 30 days, regardless.

• ###### 13. Re: How to get last 30 day average

I'll try to explain your queries,

Previous logic didn't work: because first of all we proposed the logic without seeing your data. Later on you shared screenshot. Seeing your screenshot we found you've put some dimension filters so we proposed to put it on context. However it didn't help. Finally you shared sample workbook and we identified the granularity of calculation behind the Total amount like you're only considering Order type and on top of that we've to considered the dates.

To make it dynamic: Yeah we can make it dynamic. While using FIXED {} you've to make sure your filters are on context so that your filters shows correct values. However, if you will add more level of details going forward in your view then to make your calculation dynamic then I'd suggest use INCLUDE{} instead of FIXED{}. Syntax will be same, use INCLUDE in place of FIXED. While using INCLUDE you don't need to put your filters on context.

Hope this help.

Mahfooj

1 of 1 people found this helpful