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).
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...
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.