Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
evansabres
Specialist
Specialist

Max/Min/Avg

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.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

11 Replies
MK_QSL
MVP
MVP

Create a Pivot Table

Dimension

eventDate

Sample Date


Expression
COUNT(DISTINCT event)

evansabres
Specialist
Specialist
Author

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
MVP
MVP

Remove Distinct word

evansabres
Specialist
Specialist
Author

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
MVP
MVP

Better if you provide some sample data...

evansabres
Specialist
Specialist
Author

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
MVP
MVP

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

evansabres
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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