topic Peek for charts in QlikView

Peek for charts

cbushey1 — Tue, 22 Sep 2015 17:35:31 GMT

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?

Re: Peek for charts

sinanozdemir — Tue, 22 Sep 2015 17:47:14 GMT

Hi Chase,

This is the front-end solution:

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

Hope this helps.

Thanks

Re: Peek for charts

pamaxeed — Tue, 22 Sep 2015 18:10:49 GMT

Hi,

here you have the script solution .

Hope it is what you are looking for.

Regards,

Patric

Re: Peek for charts

sunny_talwar — Tue, 22 Sep 2015 18:16:21 GMT

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

Re: Peek for charts

cbushey1 — Tue, 22 Sep 2015 20:02:09 GMT

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.

Re: Peek for charts

sunny_talwar — Wed, 23 Sep 2015 03:30:27 GMT

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