7 Replies Latest reply on Oct 13, 2013 6:35 PM by Shawn Wallwork

# Hi there, I am struggling to build a formula (I'm a beginner)...

Hi there, I am struggling to build a formula ('m a beginner)...I need the formula to be a range of "days" that counts the number of sales made for each day eg: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday. Currently, I have this formula: ZN([Last Weeks Count]) - LOOKUP(ZN([Last Weeks Count]), -1), however, this formula is giving me info from "last week", I need it to provide info from the days specified. CAN YOU PLEASE HELP ME???!!!

• ###### 1. Re: Hi there, I am struggling to build a formula (I'm a beginner)...

Nicholas, see attached.

Cheers,

--Shawn

• ###### 2. Re: Hi there, I am struggling to build a formula (I'm a beginner)...

HI Shaun,

Thank you for your quick reply. I tried what you have suggested, however, I am missing a column name?

I am just after a simple formula stating all sales to show within a specified range of days being Monday through to Sunday.

• ###### 3. Re: Hi there, I am struggling to build a formula (I'm a beginner)...

Nickolas,

If you make you measure discrete and move it from the text shelf to the row shelf you'll get a column name:

What about the above table doesn't meet your requirement? If you take the table calculation off you'll get this:

That's just a count of records for each day of the week. Then you can use a date filter on the Filter Shelf to limit it to a specific month. If you can use the workbook I posted to mock up what you're looking for I'll be happy to help.

--Shawn

• ###### 4. Re: Hi there, I am struggling to build a formula (I'm a beginner)...

Hi Shawn,

My apologies for not replying sooner.

Below is the custom formula that was pre-built which is not showing the right data.

IF DATEDIFF('week', [Deposit Taken Date] , Today()) - 1 = 0 THEN 1

ELSEIF DATEDIFF('week', [Settlement Date] , Today()) - 1 = 0 THEN 1

ELSEIF DATEDIFF('week', [Exch Date] , Today()) - 1 = 0 THEN 1

ELSE null

END

I need the formula to express - Monday, Tuesday, Wed, Thurs, Fri, Sat, Sun last week as these are the days our sales offices are open, therefore i need to show all sales made on these days.

Shawn, can you assist? I'm struggling here being new to all this..

• ###### 5. Re: Re: Hi there, I am struggling to build a formula (I'm a beginner)...

So this is a workbook you inherited? See if this is what you're looking for:

IF DATEDIFF('weekday', [Deposit Taken Date] , DATETRUNC('week', Today())) - 6 = 0  THEN 1

ELSEIF DATEDIFF('weekday', [Settlement Date] , DATETRUNC('week', Today())) - 6 = 0  THEN 1

ELSEIF DATEDIFF('weekday', [Exch Date] , DATETRUNC('week', Today())) - 6 = 0  THEN 1

ELSE null

END

Before the formula was looking back 1 week. Now the formula first truncates Today to the first day of the week, which in Tableau world is always Sunday, and then looks back 6 days to the Monday, because that's what you said you wanted to be the first day of the week. Right? You'll need the Weekday on the column or row shelf to get all this to work. (See attached.)

Also this is just giving you a count of records, not a sum of sales. You could replace the 1 with SUM([Sales]) to get a total of sales on each weekday last week.

Let me know if you have other questions.

--Shawn

• ###### 6. Re: Re: Hi there, I am struggling to build a formula (I'm a beginner)...

Hi Shawn,

I copied your formula, however, this gave me no result, leaving the columns blank.

This has got me baffled..

I do really appreciate your assistance though.

Regards

Nick

• ###### 7. Re: Re: Hi there, I am struggling to build a formula (I'm a beginner)...

Did you look at the workbook I posted?