Solved: Re: Max/Min/Avg - Qlik Community

evansabres · ‎2015-03-11

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.

MK_QSL · ‎2015-03-11

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)

View solution in original post

MK_QSL · ‎2015-03-11

Create a Pivot Table

Dimension

eventDate

Sample Date

Expression
COUNT(DISTINCT event)

evansabres · ‎2015-03-11

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?

MK_QSL · ‎2015-03-11

Remove Distinct word

evansabres · ‎2015-03-11

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

MK_QSL · ‎2015-03-11

Better if you provide some sample data...

evansabres · ‎2015-03-11

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

MK_QSL · ‎2015-03-11

What is the result or final table you are looking for?

evansabres · ‎2015-03-11

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.

evansabres · ‎2015-03-11

Also, I would like to know the max, avg and number of tickets posted for an event by day