11 Replies Latest reply on Nov 5, 2018 8:02 AM by Tony Fahd

# How to group services based a date argument?

Hi, so i am attaching a workbook where i have a list of my customers and their consult type. each customer will have multiple consult type. i am trying to group customers who had a strategy consult  and group all their consults that fall between  the initial strategy date. so for my customer A below, i want to be able to group all consults that took place between the from date and to date of their strategy consult. same thing for my customer B

my end result will be something like that: i like to group it on the consult type which is strategy and have a second column that has all consults that fall between the from date and to date of the strategy consult.

hope it makes sense and this is very complicated for me.

any help is appreciated.

Tony

• ###### 1. Re: How to group services based a date argument?

Like This?

Or

This

1 of 1 people found this helpful
• ###### 2. Re: How to group services based a date argument?

No, I think Tony is looking for this:

Tableau 10.5 TWBX attached.

1 of 1 people found this helpful
• ###### 3. Re: How to group services based a date argument?

Deepak, the second screenshot is what I am looking for. Can you share the functions for calculations 1 and 2? or mabe the workbook?

can I remove customer from the second screenshot and keep calculation 1?

• ###### 4. Re: How to group services based a date argument?

Here it is

1 of 1 people found this helpful
• ###### 5. Re: How to group services based a date argument?

Deepak and Zah,

this is exactly what I was looking for. I selected Zach as the correct answer because i wanted to limit all consults to the date in between the intial strategy date and that's what Zach function did.

you guys are data magicians! Thanks for the help

Tony

• ###### 6. Re: How to group services based a date argument?

If some customers have multiple strategy consults, but you only want to see additional consults during the first, you may want to adjust the formula for [strategy end] from

{ FIXED [Customer]: MAX(IF [Consult]="strategy" THEN [To Date] END) }

to

{ FIXED [Customer]: MIN(IF [Consult]="strategy" THEN [To Date] END) }

• ###### 7. Re: How to group services based a date argument?

i see. what about if customers have multiple strategy consults and i want to group all consults for all the strategy consults?

• ###### 8. Re: How to group services based a date argument?

That's a lot trickier. One way is to cross join your data source on customer, so that every consult can get compared against every other consult to see if it is within a strategy window. I mocked up some data in Excel, pasted in the clipboard twice so I could cross-join it, and the results look correct. Tableau 10.4 TWBX attached.

1 of 1 people found this helpful
• ###### 9. Re: How to group services based a date argument?

i see, i have to split my data into tables or do the joints in SQL outside Tableau than move my data to Tableau.

• ###### 10. Re: How to group services based a date argument?

You can do the cross-join in Tableau. Go to the data source window, duplicate your existing connection, and join them on Customer. I attached a new workbook using your original data.

1 of 1 people found this helpful
• ###### 11. Re: How to group services based a date argument?

I see what you did. Thanks Zach, your functions and logic work wonder.

Tony