6 Replies Latest reply on Apr 5, 2018 6:33 AM by Moira Reade

# Sum of sales in a month and compare to a moving average

I'm uploading an example using superstore of how I'm currently displaying the Sum(sales) for subcategories during a month with a parameter to select how many to display.

Now what I'd like to do is add a reference mark for the average sum(sales) for the same subcategories over the past 3 months.

I cannot wrap my head around how to do it.  Can anyone help?

Thanks!

• ###### 1. Re: Sum of sales in a month and compare to a moving average

Good morning

The avg is a Window_Avg - because Rank is a table calculation (at the bottom of the order of operation) you will need to use another table calc to get the avg

it returns this when plotted with dual axis

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Sum of sales in a month and compare to a moving average

Thanks Jim: I was looking for an average back in time for EACH subcategory, rather than an average of the bars in the view (which is what you provided I think).

• ###### 3. Re: Sum of sales in a month and compare to a moving average

Hi, Moira

Please find my solution attached and below screenshot of result

Please let me know if you have question

ZZ

2 of 2 people found this helpful
• ###### 4. Re: Sum of sales in a month and compare to a moving average

Sorry for my first response - I re read what you wanted

see the attached

this gets a little complex

the 3 month average is

and it is calculated like this

then the viz is constructed like this

when you construct viz it will look like this

in the red box is the LAST() values - highlight all columns except last()=0 and HIDE (right click and select HIDE

this keeps the past months in play to determine the 3 month average but hides them in the view

you can then click on the Last pill and unclick show header so that you don't see the 0

and the result looks like this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 5. Re: Sum of sales in a month and compare to a moving average

That's it!  Thanks. I figured it had to be something with a date parameter and datediff...but I just couldn't get there.

Thanks again.

Moira

• ###### 6. Re: Sum of sales in a month and compare to a moving average

Wow!  That is a bit complex. It does look very interesting though. I will digest in detail.  In the meantime the response by ZZ is pretty intuitive to me and gets me there.