14 Replies Latest reply on Sep 29, 2013 8:18 AM by Craig Mullins

# combined sets based on dates

 Subject combined sets based on dates Description i am trying to isolate all new donors in fye 6/2014 by combining sets. i have set 1 with all donors who have given before 7/1/14. i have set 2 with all donors who have given before 7/1/13. i want to combine the 2 sets, subtracting set 2 from set 1, leaving only those new donors in FY 2014. when i attempt to combine the 2 sets: i can select set 1 but set 2 does not appear in the drop down. why does set 2 not appear? can i not combine date based sets? thanks
• ###### 1. Re: combined sets based on dates

Craig,

The two sets you want to combine have to be based on the same dimension (dates are okay).  I'm guessing you are using two different date fields.  In that case, the option to combine won't be there.  I'm going to spend a bit of time thinking about whether there is a way around that...

Regards,

Joshua

• ###### 2. Re: combined sets based on dates

Actually, now that I re-read what you posted I may need some clarification.  If both sets are based on Donor and simply using a condition based on the date field, then I'm not sure why you wouldn't be able to combine them.  I was able to do this with sample data.  Do you happen to have a packaged workbook you can share?

Also, what is your data source?

Regards,

Joshua

• ###### 3. Re: combined sets based on dates

could you post your sample data twbx.

my packaged workbook has confidential data - if i cannot resolve the issue with you response i will try to extract some redacted data for a new twbx

the data source is excel , 90,000 rows

thanks

• ###### 4. Re: Re: combined sets based on dates

Craig,

I've attached a workbook with two calculated sets and a combined set.  One with customers having a minimum order date less than or equal to 1/1/2013 and another with customers having a maximum order date greater than or equal to 1/1/2013.  The combined set is the set of shared members.  It seems to work fine.  There does not seem to be any limitation with a live Excel connection.

Regards,

Joshua

1 of 1 people found this helpful
• ###### 5. Re: Re: combined sets based on dates

are the date sets derived by  a date filter applied to the entire data ?

• ###### 6. Re: combined sets based on dates

Craig,

Yes, they are created using the condition which creates a set based on the entire underlying data set (although a context filter would be applied first to define the context).

Regards,

Joshua

• ###### 7. Re: Re: combined sets based on dates

still not extracting a single years new customers. see attached. my date filters must be set incorrectly.thanks

• ###### 8. Re: Re: Re: combined sets based on dates

Craig,

If I'm understanding your goal, I think you can do this using only one set.

To get a set of customers that had their first order in 2012, you can use the formula:

MIN([Order Date]) >= #1/1/2012#

AND

MIN([Order Date]) < #1/1/2013#

To determine customers who placed their first order in 2012.  That Set can then be used as a filter for Customer and then you can additionally filter the Order Year if you only want to see 2012.

Technically, the set isn't even needed for this particular view as you could have a conditional filter on the Customer field -- but the set might be very useful in other analysis.  I've reattached the workbook.

It's quite possible that we've jumped around a bit on what you are trying to accomplish and that I've missed your ultimate goal -- so feel free to correct any wrong assumptions on my part.

Regards,

Joshua

1 of 1 people found this helpful
• ###### 9. Re: combined sets based on dates

the formula does work but why does it need the additional Year (order date) filter.

your formula appears to exclude orders after 2012, yet 2013 orders still appear?

• ###### 10. Re: combined sets based on dates

Craig,

That's a good question.  The reason is that the formula gets the set of customers whose first order (min order) is in 2012, but many also had later orders in 2013 so the additional filter narrows the focus to 2012.

Regards,

Joshua

• ###### 11. Re: combined sets based on dates

Because the set is returning the customers that meet that criteria; the filter tells Tableau to only worry about the dates in that year.  Basically, the set uses the date to arrive at those customers, but does not filter on those dates.

1 of 1 people found this helpful
• ###### 12. Re: combined sets based on dates

THANKS for the HELP !!!

• ###### 13. Re: combined sets based on dates

You're welcome!

• ###### 14. Re: combined sets based on dates

one more very basic Q:

your SQL:  MIN([Order Date]) >= #1/1/2012# AND MIN([Order Date]) < #1/1/2013#

can you give me a layman's translation of the formula - because it looks like this alone should isolate the new customers in a single period.

also how could i set up a side filter or slider to be able to change the period on the fly?

thanks