7 Replies Latest reply on Feb 12, 2019 5:01 AM by Sirisak R. Anotai

# Yearly Sales,Last week sales and Last 4 weeks Sales

Hi guys, I want to see the following three measures in a single chart. How can I get the the values for yearly Sales, Last week sales and Last 4 weeks sales in one chart. can anyone help me with the calculation for  the same so that I can see all of them at once and their growth Percentage as compared to the same time previous year.

• ###### 1. Re: Yearly Sales,Last week sales and Last 4 weeks Sales

can you draw the output on the excel, so that I would know how exactly you want the view?

-Ashish

• ###### 2. Re: Yearly Sales,Last week sales and Last 4 weeks Sales

some info:

last week sales :

IF datediff('week',[Order Date],DATE('06/20/2016'))>0 and

DATEDIFF('week',[Order Date],DATE('06/20/2016'))<2 THEN [Sales] ELSE NULL END

last 3 week sales

IF datediff('week',[Order Date],DATE('06/20/2016'))>0 and

DATEDIFF('week',[Order Date],DATE('06/20/2016'))<4 THEN [Sales] ELSE NULL END

YTD sales;

if orderdate =today()

and datedfiff('year',orderdate,today())=0 then sales end

previous year sales:

if orderdate =today()

and datedfiff('year',orderdate,today())<1 then sales end

;

previous year sales using month :

If DATEDIFF('month',[Order Date],TODAY()) <= 12

and DATEDIFF('month',[Order Date],TODAY()) >= 0

then [Sales]

END

%difference calculation:Paint By Numbers: Tableau Tip - Comparing a custom date range with the prior year

1 of 1 people found this helpful
• ###### 3. Re: Yearly Sales,Last week sales and Last 4 weeks Sales

Hi ameya v,

Calculations that I have used are as follows:

1. Previous_Week_Sales

If [Order Date (Week numbers)]=DATEADD('week',-1,[Week]) and YEAR([Order Date (Years)])=YEAR([Year]) then [Sales]

END

2. Last 4 Weeks_Sales

If [Order Date (Week numbers)]<=DATEADD('week',-1,[Week]) and YEAR([Order Date (Years)])=YEAR([Year]) and [Order Date (Week numbers)]>=DATEADD('week',-4,[Week])  then [Sales]

END

3. YTD_Sales (Till selected week)

If YEAR([Order Date (Years)])=YEAR([Year]) and [Order Date (Week numbers)]<=[Week]  then [Sales]

END

I have created Custom Dates for Years and Week as below. Please don't forget to make it. For Years refer below screenshot.

Select year in the windows that popped out after clicking on the Custom date. Select Week Numbers for weeks. I have added this dates in the Parameter named as "Year" and "Week".

Let me know if you have any difficulty.

Thanks and Regards,

Ashish Chaudhari

1 of 1 people found this helpful
• ###### 4. Re: Yearly Sales,Last week sales and Last 4 weeks Sales

Hi  ashish

Thankyou learnt new thing about custom date

• ###### 5. Re: Yearly Sales,Last week sales and Last 4 weeks Sales

. Even I have learned it from community few days back. I had no clue about this earlier.

• ###### 6. Re: Yearly Sales,Last week sales and Last 4 weeks Sales

Hi, I know that this post is a bit old but... just in case anyone needs a DYNAMIC way to get the last week value, I have found an easier way to get the last week values.

You create the following calculated field

if [Date] >= DATEADD('week',-1, DATETRUNC('week', TODAY()))

AND [Date] <= (DATEADD('week',-1, DATETRUNC('week', TODAY())) + 6)

THEN [Sales] ELSE 0 END

So in this way, you don't depend on parameters and you can get the values from the last week automatically. You could do the same with months, quarters...

In order to get the last 4 weeks I would do something like:

if [Date] >= DATEADD('week',-4, DATETRUNC('week', TODAY()))

AND [Date] <= (DATEADD('week',-4, DATETRUNC('week', TODAY())) + 7*3+6)

THEN [Sales] ELSE 0 END

(haven't tested the last one but should be like that)

Regards!

1 of 1 people found this helpful
• ###### 7. Re: Yearly Sales,Last week sales and Last 4 weeks Sales

Thank you, I try a long time to find this calculation "WOS"