Counting records by two different date fields in single table?

I have a table with our support tickets and I'm trying to show a weekly chart of both how many tickets are opened and how many are closed (each ticket record has a field for opened date and one for closed date). I can easily show one at a time (just counting grouped on the appropriate date), but is it possible to show both at the same time?

Alternatively, I have two tables (one is the raw ticket table, one is a worksheet based on the table) and I could include both in my chart, but Thoughtspot doesn't let me include both because there's no relationship between them (even though I'm counting two different things, so there shouldn't be a relationship).

Any ideas?

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Ryan.. This is quite a common scenario and can be solved using an intermediate event table, where there is an eventId for each event(closed,opened,etc) and event date and event type(closed,opened) and ticketId.. You can establish a relationship from the Event table to the Ticket table.. Then a search like count of tickets daily(on eventdate) aggregated by event type should give you the desired result.. Let me know if this clarifies...

    Like
  • I was picturing something like this and it seemed like I couldn't do it from the native table. Next question - is it possible for me to create a worksheet in TS that unions together multiple copies of a single table, or will I need to create a new table and load this data separately? Ideally I'd like to do this with data I already have, but I don't see a supported way to do that.

    Thanks!

    Like
  • It will require some TQL magic to achieve this.. I think it should be possible with the Create Table as Select(CTAS) construct.. However, I will need to try it out before confirming this to you...

    Like
  • Ryan McCauley 

    Hi Ryan, 

    You can create multiple views of your table.  Once you've created those views, you can join them just like tables.  Views are saved searches, so you can do this without adding any extra data to your system.

    Like
Like Follow
  • Status Answered
  • 11 mths agoLast active
  • 4Replies
  • 50Views
  • 3 Following