Chart with Dates

[evansabres]

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.

[sunny_talwar]

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

[evansabres]

How would I structure the script?

[maxgro]

try the attachment

[evansabres]

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?

[maxgro]

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

C:

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

[evansabres]

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

[sunny_talwar]

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.

[evansabres]

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

[sunny_talwar]

What would be the ouput from this?