Using LODs to Compare a Month to the Historical Monthly Average

#TableauTipTuesday: Using LODs to Compare a Month to the Historical Monthly Average

Read more and download the workbook here -

[michafabich]

Hi Andy extremly useful! Appreciate your huge work and I am extremely grateful that you are sharing your knowledge with people.

[smallypuppy]

Than you Andy! This was extremely helpful, I feel like this is the starting point to understand LOD expressions and table calculation.

[sebrancourt]

I really like your videos and often use them for ideas and learning opportunities. If I may suggest one thing, it would be to say whether the actions you are performing (i.e. LOD) require the user to set their data as an extract rather than using live data. It took me a little while to figure out that the LOD functions only work with data extracts, they cannot be performed on live data. Thanks and keep the tips coming!

[SG-sjqh]

Thanks Andy, it was helpful. Could you also share video to calculate weekly averages

[ronaldmrenwickjr]

Awesome video!

I would like to do something similar, but I would like to compare a monthly expense to the prior 12 month average. So comparing May 2023 results to the average of May 2022 through April 2023.

would that require a similar approach? or am I going down the wrong path here?

[prachiwanjari]

Your videos are very helpful...
I m finding same sort of tutorials for alteryx also so that i can go for every details of topics felated to alteryx also

[DanielGetu]

Andy,
You are an amusing tableau VI expert! I love the way how you explain. I learned a lot from you. I’m thinking to take the tableau desktop associate exam. Do you have a book or suggest me one that can assist me to pass the test?

[fusionarun]

Hi Andy, keep creating more videos like this. Love your work!

To calculate the Monthly avg. sales, I used the below expression

({FIXED MONTH([Order Date]) : Date]))} and it yielded me with same result. What do you think about this?

[andrewmenges]

Hey Andy - very helpful! Would it possible to exclude a year from this average? for example, let's just say 2020 was a very anomalous year... and we didn't want that to factor into the the averages when comparing to 2021. Thanks!!

[alexbourdeau]

Is there a way to know if the difference between years is statistically significant?

[mosesmadrigal]

What about Monthly Historical average fixed on Product Name?

Would it be just an additional level on the LOD equation?

i.e.
{FIXED DATEPART ('month', [Date]), [Product Name] : AVG(
{FIXED DATETRUNC ('month', [Date]) : SUM ([Sales])})}

[kayma]

how would you do days in a month, for e.g. December 2017 - January 2018 compared with December 2018 - January 2019.

[jpovikes]

how would I take most recent month sales, sum that up and compare to the prior year same month sum sales but always have tableau read the most recent month. example feb sales this last month were 100k and feb sales last year were 90k. 90k would be my basis of comparison so my grow would be about 11%. next month I would like to compare mar sales to mar sales always having prior year's month as the basis of comparison but don't want to have to update years or months would like it to happen systemically when I bring in the new data. any links to examples or help on this would be greatly appreciated

[priyanshagarwal]

Hi Andy, How are able to filter data, for most of the calculations, you are using FIXED and fixed is not affected by dimension filters so, how come you are able to filter the data?

[vishwanathrana]

How do i compare avg of first 2 month (jan+feb) values of 2020 to every month(mar-current month) of 2020 to see %age achieved.
this is basically to compare monthly performance of all months to the first 2 months combined ??
i cant the aggregate values( avg of first 2 months) to divide the monthly count to get the percent achieved. either it gives weird calculation or it just gives blank .
any help is much appreciated!!

[andrea]

Excuse me sir, do you have a tutorial to make a trend line of week over week comparison? (so the lines are 2: selected date and same date in previous 7 days)?

[prachiwanjari]

Sir,
I am having issues with connecting Oracle database..
Can you explain in any video how to connect with server.

[karenh]

This is a great analysis. In my spend analysis views I have parameters to select procurement category or department, so I tried extending this LoD to include those dimensions--without success. Any suggestions?

[rp]

I wish you could explain the meaning behind half the functions you are using.

[mmmaxmmm]

Hi all, how do I deal with the fact that in Tableau, table calcs like difference from, etc do not like it when you have a 0 or null value. So one year sales is $1M and the next year for that category, sales is $0 or null. Can you write a table calc to deal with that?