CODEWITHSUNDEEP
tableau-api
lilloraffa
lilloraffa

Reputation: 1397

Tableau - Current year, moving average and previous year figures, for the last 2 months

I've exhausted my creativity... so here I am. I need to create a line graph showing daily figures of the last two months from today of: - this year sales - this year moving average sales - previous year moving average sales

It seems pretty straightforward, but I tried all i could and was not able to find the right filter/data combination. Can anybody help me with that, please? Thanks

Upvotes: 0

Views: 22711

Answers (2)

user4553928
user4553928

Reputation: 1

Create 2 filters:

1 with the last 2 years in there The other with something along the lines of 

if(datepart('month', [reportDate]) = datepart('month', Today()) then 
    1 
else 
    0 
end.

(this is only the current month, update the code to also take last month into account with the Dateadd() function.

Filter on last 2 years, and filter the second filter one value 1.

Upvotes: 0

Inox
Inox

Reputation: 2275

It's VERY simple.

Drag your date field to columns, and the measure you want to plot on rows.

Then right click the measure, and Add Table Calculation

Choose Moving Calculation, Summarize using Sum, Moving along Table (Across), previous Values 60 (2 months), Next Values 0, and voilà

Table Calculations are powerful. Learn how to use them

http://www.tableausoftware.com/pt-br/table-calculations http://kb.tableausoftware.com/articles/knowledgebase/Table-Calculations http://onlinehelp.tableausoftware.com/v6.1/public/online/en-us/ideba0d507-6dee-41cb-983a-4267e37a4566.html

EDIT: To answer clarification

Okay, so your problem is probably (you could be more specific by the way, show exactly what you're doing, what you're getting and what you aspire to get) that when you filter out the other months (rather than August or July) you lose the information (because you cannot assess information from 2 previous months).

One simple way to cope with that is to actually not filter any data, and just hide the columns you don't want to show (select all months you don't want to show, right click and hide).

A more elegant way to do that (and more interactive way) is to create a table calculation to be used as filter. This technique takes advantage of the fact that table calculations are performed last.

If you intend to show only July and August, you create a field called filter:

MONTH(LOOKUP(MAX(Date)),0) = 7
OR
MONTH(LOOKUP(MAX(Date)),0) = 8

The lookup(max(date),0) will basically return the date (better, the max date of each day, as you have days on screen). Filter on True, and only July (7) and August (8) will continue to be on screen, but it will show the running sum from 2 months to date

The main difference from doing MONTH(Date) directly is that using lookup will force Tableau to calculate this last, so you won't filter out the data from your running sum calculation, only from the visualization. A little hack, but it works

To get a better filter, you can use parameters, so it's more interactive.

I

Upvotes: 3

Related Questions