7 Replies Latest reply on Jul 5, 2016 2:11 PM by sameer.palladwar

# logical test with today() -1 = stored date then sales

hi folks,

I have field in database 'calday' which maps to fiscal months and fiscal days. for eg -

I want to check if current date -1 = calday and fiscal month = current mont and fiscalday <= currntday then sales

i have done same mapping in tableu for current date -1 but it is an inefficient way to do, please suggest

• ###### 1. Re: logical test with today() -1 = stored date then sales

Hi Sameer,

can you share the sample data shown in the screenshot? It would be easier to do it then discuss.

Thanks and Regards,

Ashish Chaudhari

• ###### 2. Re: logical test with today() -1 = stored date then sales

HI Sameer

I want to check if current date -1 = calday and fiscal month = current mont and fiscalday <= currntday then sales

In Tableau we have the TODAY() function which can be used.

So for yesterday its simply TODAY()-1

And in a logical test -

IF [calcday] = TODAY()-1 THEN TRUE ELSE FALSE END

We can then extend that...

IF [calcday] = TODAY()-1 AND MONTH(TODAY()) = [fiscal month] AND [fiscalday] <= TODAY() THEN [sales] ELSE NULL END

Important - you need to make sure these fields are dates - they should have a little calendar icon.

I hope the above is clear, it may not be perfect but I can help you with specifics...

Cheers

Mark

Edited to finish extended IF calc.

• ###### 3. Re: logical test with today() -1 = stored date then sales

Hey guys, thanks for the reply, I have tried this methods already. However, it returns null and somehow the calc does does not work.

Here's a sheet, clac Fields FY15 sales is what I am trying to achieve. Let me know if you guys figure some way

• ###### 4. Re: logical test with today() -1 = stored date then sales

Hi Sameer

Right idea, but you need to provide a packaged workbook

File - Export packaged workbook

We want one with a twbx file extension, otherwise we dont have any data.

Cheers

Mark

• ###### 6. Re: logical test with today() -1 = stored date then sales

I want to check if current date -1 = calday and fiscal month = current mont and fiscalday <= currntday then sales

This works fine -

IF TODAY()-1 = [Calday] THEN [Sales] END

The problem -

fiscal month = current mont

fiscal month for 'nowday' is 2

but month of July is 7           2 <> 7

fiscalday <= currntday then sales

fiscal day for nowday is 6

but day of current day is 5 (calcday = 4)      6 is greater than 5 (or 4), not less, this will also fail

Not sure where / how to help?!

• ###### 7. Re: logical test with today() -1 = stored date then sales

hey buddy,

1. IF TODAY()-1 = [Calday] THEN [Sales] END   (works fine)

2. fiscal month = nowday month (required)

3. fiscal day = nowday day (required)

FYI,  nowday month is (if TODAY()-1 = [Calday] then fiscal month)

nowday day is (if TODAY()-1 = [Calday] then fiscal day)

2 & 3 does not seem to produce desired results, check the workbook shared.