Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi Chase,
This is the front-end solution:
Let me know if you need the back-end solution as well.
Hope this helps.
Thanks
Hi,
here you have the script solution .
Hope it is what you are looking for.
Regards,
Patric
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
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.
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