1 of 1 people found this helpful
The issue with your first statement is that you're not using the IF statement to set the field to anything. If you think of the table, if 50% of your rows are ID 1, you're trying to create a new variable where, for those records, it is ">=23/06/2006", which doesn't make sense.
Instead what you want is to have the Trans_Date field show up only if for that ID it falls between the Start Date and End Date. As you've set it up, a Case statement won't work (it can only use one variable at a time), so switch to an IF/THEN:
IF [ID]=1 AND ([Trans_Date] >= #06/23/2006# AND [Trans_Date] <= #07/15/2006#) THEN [Trans_Date]
ELSEIF [ID]=2 AND ([Trans_Date] >= #03/19/2007# AND [Trans_Date] <= #04/22/2007#) THEN [Trans_Date]
All other values (e.g. values for those IDs not in between the Start and End dates) will be null, so will not show up in your data.
I don't know your data, so this would likely need tweaking (is ID unique; if not are the dates fixed per ID, etc.) - but if you want it to not a little more fluid, you can replace with variables, something like:
IF [ID]=1 AND ([Trans_Date] >= [Start Date] AND [Trans_Date] <= [End Date]) THEN [Trans_Date]
ELSEIF [ID]=2 AND ([Trans_Date] >= [Start Date] AND [Trans_Date] <= [End Date]) THEN [Trans_Date]
You may also be able to remove the ID field - but you'll have to adjust based on your data and how you want it displayed.
Hope this helps!
Thanks a lot for that, its real helpful. I think I understand now. I've only given two IDs as an example, but actually have 135 different IDs with different Start and End Dates.
Should I just do 134 ELSEIF statements?
How many ELSEIFs can Tableau handle?
Actually, it should work just fine by taking out the ID restriction - I tested on some sample data.
IF ([Trans_Date] >= [Start Date] AND [Trans_Date] <= [End Date]) THEN [Trans_Date]
This will work on a row-by-row basis, so as long as your data is setup like the above, this should work. By the way, you'll want to create one for any variables you're showing, besides Trans_Date. For example:
IF ([Trans_Date] >= [Start Date] AND [Trans_Date] <= [End Date]) THEN [Volume]
A lot less work than 134 ELSEIF statements But for your question on how many Tableau can handle: I do believe it doesn't have a cap on the number you can have (like with SQL), but you may see performance impact after the first few hundred....