8 Replies Latest reply on Jan 22, 2019 3:34 AM by Roger Pouly

# Minimum number of sales of the week - How to do it ?

Hello everyone,

I'm trying to do something that seems fairly simple but I cannot seem to succeed in finding something that works.

I have [Order Date] and [Order ID], and I want to identify the minimum number of sales of each week.

Exemple :

Days
SalesMinimum sales of the week

Monday 1st Jan

13 sales
 7 sales

Tuesday 2nd Jan

18 sales
 7 sales

Wednesday 3rd Jan

7 sales
 7 sales

Thursday 4th Jan

25 sales
 7 sales

Friday 5th Jan

12 sales
 7 sales

Saturday 6th Jan

10 sales
 7 sales

Sunday 7th Jan

13 sales
 7 sales

Monday 8th Jan

22 sales
 14 sales

Tuesday 9th Jan

14 sales
 14 sales

Wednesday 10th Jan

18 sales
 14 sales

Thursday 11th Jan

19 sales
 14 sales

Friday 12th Jan

18 sales
 14 sales

Saturday 13th Jan

29 sales
 14 sales
Sunday 14th Jan18 sales
 14 sales

For starters, I tried to calculate the total number of orders per week with this :

{FIXED DATEPART('week', [Order Date]) : COUNTD([Order ID])}

But I am having trouble using this as it doesn't seem to compute the way I want.

Any insight ?

Thanks,

Roger Pouly

• ###### 1. Re: Minimum number of sales of the week - How to do it ?

Hi Roger,

The following calculation will likely get you where you need to be.  My week starts on a Sunday whereas yours might be on a Monday, hence slight difference in results:

{FIXED DATEPART('week',[Date]): MIN([Sales])}

1 of 1 people found this helpful
• ###### 2. Re: Minimum number of sales of the week - How to do it ?

Hi Don,

Sadly I need to calculate the number of sales, and not the sum sales themselves. To do this I need to use Countd and not Sum, and this is where it doesn't work anymore for me.

I've attached a workbook.

Thank you

Roger

• ###### 3. Re: Minimum number of sales of the week - How to do it ?

Hi Roger,

Sorry about the misunderstanding, I was going off the screen example.  Could you repost your workbook?  Appears it was created in 2019.1 version and I can't open it.  Error message is:

Unable to complete action

Errors occurred while trying to load the workbook "/Users/donwise/Downloads/Count min number of sales per week.twbx". The load was not able to complete successfully.

This file was created by a newer version of Tableau. Please upgrade Tableau to the version (or greater) that the file was saved in. ( Incompatible document Unrecognized Format Changes: SheetIdentifierTracking )

I have 2018.3. Go to File, Export as Version, you'll be presented with options to choose from.

Thx!  Don

• ###### 4. Re: Minimum number of sales of the week - How to do it ?

Hello Don,

You will find here a 2018.3 version of my file.

Thank you

Roger

• ###### 5. Re: Minimum number of sales of the week - How to do it ?

Hi Roger,

Got it and thanks!  Try a Window_MIN calculation (newly attached 2018.3 workbook).  See if that gets what you're looking for? This involves use of a window/table calculation and then setting it to restart each week.

Minimum number of sales per week correct?  Thx, Don

1 of 1 people found this helpful
• ###### 6. Re: Minimum number of sales of the week - How to do it ?

Thanks a lot Don Wise, it works.

The drawback here compared to {FIXED DATEPART('week',[Date]): MIN([Sales])} is that you have to have it in your view in order to calculate it. It does answer my prompt though, so I will mark your answer as correct. If someone has an idea for a calculated field that work no matter the view I am still interested.

Thanks a lot for your help,

Roger

• ###### 7. Re: Minimum number of sales of the week - How to do it ?

Hi Roger,

Please try the below formula. it works for sure.

{fixed datetrunc('week',[Order Date]): min({fixed DATETRUNC('day',[Order Date]):COUNTD([Order ID])})}

Regards,

Prabhakar.

1 of 1 people found this helpful
• ###### 8. Re: Minimum number of sales of the week - How to do it ?

Hello Prabhakar,

Thanks a lot, it works perfectly.

Roger