Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello -
I have a data set for a secondary market ticketing information application. There are several fields that I am trying to visualize. Specifically, the quantity of tickets posted in regards to an event date and a sample date.
For example, there is an event on 12/20/2014 (eventDate) and overall there have been 2500 tickets posted to the secondary market for this event (Quantity). Those 2500 tickets have been posted periodically to the secondary market over the past several months (Sample Date).
How can I create a visualization to show the number of tickets (Quantity) posted for an event (eventDate) over the course of time (Sample Date)
Thank you.
Use below script
Temp:
LOAD
TimeStamp(ALT(TimeStamp#(Date,'DD/MM/YYYY hh:mm'),Timestamp#(Date,'M/DD/YYYY h:mm:ss TT')))as DateTimeStamp,
Date(FLOOR(TimeStamp(ALT(TimeStamp#(Date,'DD/MM/YYYY hh:mm'),Timestamp#(Date,'M/DD/YYYY h:mm:ss TT'))))) as Date,
Opponent,
Section,
Row,
Price,
Quantity,
TimeStamp(ALT(TimeStamp#([Sample Date],'DD/MM/YYYY hh:mm'),Timestamp#([Sample Date],'M/DD/YYYY h:mm:ss TT')))as [Sample Date TimeStamp],
Date(FLOOR(TimeStamp(ALT(TimeStamp#([Sample Date],'DD/MM/YYYY hh:mm'),Timestamp#([Sample Date],'M/DD/YYYY h:mm:ss TT'))))) as [Sample Date],
Board,
ETicket
FROM YOURTABLENAME;
Now create pivot table with
Dimension
Date
Sample Date
Expression
SUM(Quantity)
Create a Pivot Table
Dimension
eventDate
Sample Date
Expression
COUNT(DISTINCT event)
Thank you. However, when I enter 'COUNT(DISTINCT event) the pivot table returns a value of 1 for every event.
Suggestions on what I am entering incorrectly?
Remove Distinct word
Not sure I am explaining what I am trying to accomplish very well.
For the example event of 12/20, there have been 2500 tickets posted. I want to know number of tickets posted for that event by sample date. Ideally, a table that shows
Event Date Sample Date Tickets Posted
12/20/2014 11/1/14 20
11/1/14 14
11/2/14 4
11/3/14 37
Better if you provide some sample data...
This is an example of the file that I use.
I upload a file each day and have a loop in my script to aggregate the data.
Thank you
What is the result or final table you are looking for?
Ideally, I want to be able to see each event date and the number of tickets posted for the event in terms of sample date. So, if the event on 12/20 has 2500 tickets, how many tickets were posted by sample date
Thank you for your help.
Also, I would like to know the max, avg and number of tickets posted for an event by day