Peek for charts

[cbushey1]

I am not sure the best approach to this and the simplyest way I can explain this is possibly finding a way to do what peek() does in a chart.

Essentially, I am looking to pull the previous date so that it can be used in a days since calculation. To complicate things I have to be flexible on the previous date as these dates all tie to different locations. Take this example:

EventDate    Location

01/01/15     Unit1

02/05/15     Unit2

03/15/15     Unit1

03/25/15     Unit2

From this you can see that Unit1 has two event dates (01/01/15, 03/15/15) and Unit2 has two event dates (02/05/15, 03/25/15). What I would like to do is identify how many days between each event given the selected location and if no location is selected I would return the number of days between event date above it.

I have tried a few different things and I have found that Peek() works pretty well in the script but doesnt account for what location the user might choose.

Here is the expected output:

Location Unit1 selected

EventDate     Location     DaysSince

01/01/15          Unit1             Null

03/15/15          Unit1              73

Location Unit2 selected

EventDate     Location     DaysSince

02/05/15          Unit2             Null

03/25/15          Unit2              48

Any suggestions on how best to handle this?

[sinanozdemir]

Hi Chase,

This is the front-end solution:

Let me know if you need the back-end solution as well.

Hope this helps.

Thanks

[pamaxeed]

Hi,

here you have the script solution .

Hope it is what you are looking for.

Regards,

Patric

[sunny_talwar]

Back end solution could be like this:

Table:

LOAD Date(Date#(EventDate, 'MM/DD/YY')) as EventDate,

  Location;

LOAD * Inline [

EventDate,    Location

01/01/15,    Unit1

02/05/15,    Unit2

03/15/15,    Unit1

03/25/15,    Unit2

];

NewTable:

LOAD EventDate,

  Location,

  If(Peek('Location') = Location, RangeSum(EventDate - Peek('EventDate'))) as DaysSince

Resident Table

Order By Location, EventDate;

DROP Table Table;

Output

[cbushey1]

I like both of these options but I have realized one small issue. If I want to just compare the EventDate to the previous one without regards for Location then these solutions wont work. This isnt a huge deal as I believe I could create a graph with each expression representing a different location.

[sunny_talwar]

I guess you can create a separate DaysSince for just Date or you can make Sinan‌'s front end solution to be dynamic so that you can compare it with regards to location or by disregarding location with a button click.

Ultimately it all depends on what your requirement is.

HTH

Best,

Sunny