topic Re: Chart with Dates in QlikView

Chart with Dates

evansabres — Mon, 06 Feb 2017 19:43:22 GMT

I have a field nwEVENTDTE, which is formatted as mm/dd/yyyy. I have another field called [Net Sold Qty]

What I want to do is to create a table that shows the field nwEVENTDTE less certain time intervals and the [Net Sold Qty] at those times.

So, for example

nwEVENTDTE-7 (or 14,21,28,etc for week intervals) and the [Net Sold Qty] at that time. Ideally, I want a chart that displays the following as an output.

Time Frame Net Sold Qty

1 Week ##,###

2 Weeks ##,###

3 Weeks ##,###

4 Weeks ##,###

6 Weeks ##,###

8 Weeks ##,###

10 Weeks ##,###

12 Weeks ##,###

Any suggestions on how to structure this would be appreciated.

Re: Chart with Dates

sunny_talwar — Mon, 06 Feb 2017 20:01:51 GMT

Create a new field in the script which calculates the Time Fram field.... Not sure how the raw data looks to give a better solution

Re: Chart with Dates

evansabres — Mon, 06 Feb 2017 20:04:46 GMT

How would I structure the script?

Re: Chart with Dates

maxgro — Mon, 06 Feb 2017 20:06:32 GMT

try the attachment

Re: Chart with Dates

evansabres — Mon, 06 Feb 2017 20:14:28 GMT

I think this would structure what I would want to do. How would I tie this into my current data to link to the field of nwEVENTDTE?

Re: Chart with Dates

maxgro — Mon, 06 Feb 2017 20:30:29 GMT

My date field is your nwEVENTDTE; the format is different but it sholudn't be a problem (Get the Dates Right)

My val field is your [Net Sold Qty]

I think you don't need the C table in my script; C table is for my test data

Also replace 4 with the number of week you want

load

date(makedate(2016) + rowno() -1) as date,

floor(rand()*100) as val

AutoGenerate 730;

FOR i=1 to 4

😧

LOAD

'week-$(i)' as period,

today(1) - iterno() as date

AutoGenerate 1

While IterNo() <= ($(i)*7)

;

NEXT

Re: Chart with Dates

evansabres — Mon, 06 Feb 2017 20:49:14 GMT

When I replace 'date' with my field 'nwEVENTDTE', they link, however I may have explained my question incorrectly.

When I choose an nwEVENTDTE from a drop-down list, I want the table to react to be 1, 2, 3, 4 weeks etc. from that nwEVENTDTE. So for example, if I choose an nwEVENTDTE of 12/12/2016, I want the table to display the [Net Sold QTY] when the time period = nwEVENTDTE less 1 week, 2 weeks, 3 weeks, etc.

nwEVENTDTE 1 Week 2 Weeks 3 Weeks 4 Weeks

12/12/2016 12,122 12,022 11,111 10,111

Re: Chart with Dates

sunny_talwar — Mon, 06 Feb 2017 21:55:55 GMT

This might be a good candidate for The As-Of Table‌, but it might be helpful to look at few rows of the raw data or make a qvw sample to understand your data and provide a solution which works for you.

Re: Chart with Dates

evansabres — Mon, 06 Feb 2017 22:00:29 GMT

Event Date Time nwEVENTDTE Net Sold Qty

Oct 13, 2016 10/13/2016 2

Dec. 12, 2016 12/12/2016 4

Feb. 16, 2017 2/16/2017 1

Re: Chart with Dates

sunny_talwar — Mon, 06 Feb 2017 22:01:28 GMT

What would be the ouput from this?

Re: Chart with Dates

evansabres — Tue, 07 Feb 2017 00:01:17 GMT

Ideally, I would like to have a chart (straight table) where once you select an event date (nwEVENTDTE) the table would display the number of tickets sold ([Net Sold Qty]) at each of the time intervals. So, if I pick the nwEVENTDTE of 12/12/2016, I would want a chart (table) displaying the number of tickets sold ([Net Sold Qty]) at different time intervals.

Event Date Tickets Sold 1 Week before Event Date Tickets Sold 2 Weeks before Event Date

12/12/2016 ##,### ##,###