4 Replies Latest reply on Oct 18, 2018 12:04 AM by Mark Fraser

Help required for Case Statement in Calculated field

Hi

I have re-engineered a Case statement in my workbook and do not get any results, if apply only one of these statements they don't work by themselves, however when I take my 'If' statement and trial this in it's own calculated field it works.

CASE [TIME PERIOD]

WHEN "YTD" then if [Reporting Dt] = [YTD DATE RANGE] THEN 1 ELSE 0 END

WHEN "MTD" THEN IF [Reporting Dt] = [MTD DATE RANGE] THEN 1 ELSE 0 END

WHEN "WTD" THEN IF [Reporting Dt] = [WTD DATE RANGE]  THEN 1 ELSE 0 END

WHEN "LY" THEN IF [Reporting Dt] = [LAST YEAR DATE RANGE]  THEN 1 ELSE 0 END

WHEN "LM" THEN IF [Reporting Dt] = [LAST MONTH DATE RANGE]  THEN 1 ELSE 0 END

WHEN "LW" THEN IF [Reporting Dt] = [LAST WEEK DATE RANGE ]  THEN 1 ELSE 0 END

END

Anyone got any ideas on how to rectify this.  Wanting to use this calculated field as a filter.

Fingers crossed someone can help

• 1. Re: Help required for Case Statement in Calculated field

Hi Tracy

Certainly, that 'type of' syntax should work, i just tried an example in the superstore workbook

CASE [Sub-Category]

WHEN 'Art' THEN IF [Product Name] = 'American Pencil' THEN 1 ELSE NULL END

END

To check - The output of [TIME PERIOD] is YTD, MTD etc.?

And what are the types of [Reporting Dt] and [YTD DATE RANGE] are they both dates? numbers? something else? if they dont match, it wont work

More examples are useful!

Cheers

Mark

• 2. Re: Help required for Case Statement in Calculated field

can you please attached sample workbook for the same.

• 3. Re: Help required for Case Statement in Calculated field

Thanks Mark

My parameter is set up as

The Reporting Date and MTD Date Range are both date dimensions

Result I get is below

Hopefully something simple that I have missed doing to get out the result I need.

Cheers

Tracy

• 4. Re: Help required for Case Statement in Calculated field

Hi Tracy

Thanks for the extra information!

Based on the parameter screenshot, then i think the bit in bold should work - you're testing a string against a string

CASE [TIME PERIOD]

WHEN "YTD" then if [Reporting Dt] = [YTD DATE RANGE] THEN 1 ELSE 0 END

END

the other part is the date test, highlighted bold below

CASE [TIME PERIOD]

WHEN "YTD" then if [Reporting Dt] = [YTD DATE RANGE] THEN 1 ELSE 0 END

END

I can see from your screenshot that [Reporting Dt] doesn't include time so i assume (and its good to check) that the data is DD-MON-YY, it could be DD-MM-YY (or with YYYY)

I can also see [MTD DATE RANGE] is a calculated field (i assume same for [YTD DATE RANGE] etc.) it would be good to check the format of this field - it should match the [Reporting DT] or else you should align them with DATE (Date Functions ) functions - such as TRUNC

Once we have at least ensured that the format of the items align - we can be sure its something else...

if possible - try to replicate a smaller data set, smaller function etc. it maybe easier to see the behavior, at the moment you have 6 clauses, try with just 1

and further, as you're using a logic test (IF) you need to ensure that you have results which will match TRUE...

From the workbook (and your use of parameters, YTD etc.) i assume you either have some tableau background, or you have inherited this workbook from someone else - certainly this is more 'advanced'

finally, i wrote this sometime back, maybe helpful in some small way >> Time periods in Tableau

Certainly with the communities help, we'll get there!

Cheers

Mark