topic Re: Max/Min/Avg in QlikView

Max/Min/Avg

evansabres — Wed, 11 Mar 2015 13:47:18 GMT

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.

Re: Max/Min/Avg

MK_QSL — Wed, 11 Mar 2015 13:58:28 GMT

Create a Pivot Table

Dimension

eventDate

Sample Date

Expression
COUNT(DISTINCT event)

Re: Max/Min/Avg

evansabres — Wed, 11 Mar 2015 14:19:48 GMT

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?

Re: Max/Min/Avg

MK_QSL — Wed, 11 Mar 2015 14:20:56 GMT

Remove Distinct word

Re: Max/Min/Avg

evansabres — Wed, 11 Mar 2015 14:27:57 GMT

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

Re: Max/Min/Avg

MK_QSL — Wed, 11 Mar 2015 14:33:30 GMT

Better if you provide some sample data...

Re: Max/Min/Avg

evansabres — Wed, 11 Mar 2015 15:10:46 GMT

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

Re: Max/Min/Avg

MK_QSL — Wed, 11 Mar 2015 15:20:28 GMT

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

Re: Max/Min/Avg

evansabres — Wed, 11 Mar 2015 15:35:04 GMT

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.

Re: Max/Min/Avg

evansabres — Wed, 11 Mar 2015 15:41:50 GMT

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

Re: Max/Min/Avg

MK_QSL — Wed, 11 Mar 2015 15:50:20 GMT

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)

Re: Max/Min/Avg

evansabres — Wed, 11 Mar 2015 16:02:22 GMT

thank you