Cumulative Values In the Script Using Peek()

Everyone loves cumulative values on a chart and they’re are possible with an expression using the above function.

This shows you how to add them to the data table in your script!

Useful for this but also the idea can be used to solve other problems. For example recently I had a client where actions where timestamped but they needed to know the duration between the two actions. The data couldn’t be joined so this technique was used.

Uses for this way of working with data within Qlik comes up time and time again when developing!!!

Note: I’ve recorded the videos in highest resolution possible. So please view them in 1080 or 720P (HD) for best quality. Cheers!

https://www.youtube.com/watch?v=zIVj4U9QqPQ

Hope you all find it useful and I look forward to hearing your comments and if you have any tricks and tips like this you’d like to share.

Cheers

Richard
Subscribers : Thanks for subscribing to my channel and I’d like to encourage you to visit my blog where I provide more Qlik related content.

http://www.QlikCentral.com

Note: I’ve recorded the videos in highest resolution possible. So please view them in 1080 or 720P (HD) for best quality. Cheers!

5 responses to “Cumulative Values In the Script Using Peek()

  1. Hi Richard,
    This is a very elegant script solution for inter-record comparisons.

    However, I have an example where I would like to make use of something similar but am struggling to make it work and wondered if you had any ideas to point me in the right direction – any help appreciated!!

    I have say the following data:

    PersonID, EventRank, EventName, EventDate
    1000, 1, Allocation Begins, 01/01/2015
    1000, 2, Allocation Ends, 02/01/2015
    1000, 3, Visit, 03/03/2015
    1000, 4, Assessment Begins, 04/04/2015
    1000, 5, Assessment Ends, 05/04/2015
    1000, 1, Allocation Begins, 06/06/2015
    1000, 2, Allocation Ends, 07/06/2015
    1000, 4, Assessment Begins, 08/08/2015
    1000, 5, Assessment Ends, 09/08/2015
    1000, 6, Closure Begins, 10/10/2015
    1000, 7, Closure Ends 11/10/2015

    What my client would like, for each record(row) is to have a Field (PreviousEventDate) which will return the most recent date of the Event with the next rank down that’s before the current event date for that row. So for the row with Visit (rank 3) it looks for rank 2 but retrieves the max date for these occurring before the visit row = 02/01/2015 and not 07/06/2015.
    If the rank=1 it should return a Null.

    Finally, it needs to scale so that it works for each unique PersonID.
    I tried using peeks and previouses and indeed in the front-end, Aboves and RowNos but without any joy 😦

    Hope this makes sense and thanks for your time,

    Elliot

    Like

    • Hi Elliot,

      Peek or previous should get the correct results as long as you sort the data correctly.

      Perhaps first sorted value will help here also. I’ve done a couple of posts around how that be be used. https://qlikcentral.com/2014/08/13/firstsortedvalue/

      You could reload the data into a mapping table with the first column being a Concat field of personid &’|’& event rank. The second column is the date.
      All you need to do then is applymap with the expression personid &’|’& event rank -1.

      Note the -1. It will return the date of the previous event.

      Hope that works for you.

      Take care.

      Richard.

      Like

  2. Thanks for the speedy reply, Richard.
    The ApplyMap approach using the Concat of PersonId and EventRank was an approach I tried, however as there are sometimes more than one occurrence of the previous event rank – the mapping only retrieves the first occurrence – if I use Max(EventDate) in the Mapping Load, I will get dates of that event rank which occur after the current event. What I need is the Max value which precedes the current event date – so for the record 1000, 2, Allocation Ends, 07/06/2015 i need to retrieve the date (for rank 1) 06/06/2015 and not 01/01/2015 but for the record, 1000, 2, Allocation Ends, 02/01/2015 I do need the 01/01/2015 as that is the most recent occurrence of rank 1 which pre-dates 02/01/2015.

    Hope this is clear – I know I’m missing something very obvious 🙂

    Thanks
    Elliot

    Like

  3. Why not in the mapping table group by eventid (or possibly eventname) and personid and use the max date? Then you should have the latest date and only one instance for each eventid so applymap will work.

    Like

    • I tried using this:

      MAPPING
      LOAD
      PersonID & ‘|’ & EventRank,
      Date(Max(EventDate),’DD/MM/YYYY’)
      Resident SourceData
      Group By PersonID & ‘|’ & EventRank
      ;

      but even though the Max ensures one value per concat id (PersonID & ‘|’ & EventRank) and I’m using
      ApplyMap(‘PrevDateMap’,PersonID & ‘|’ & Num(EventRank-1)
      to retrieve the maximum date for rows with rank one lower, that date may be greater than the date of the current row – I still need to retrieve the maximum date for dates less than the current row ??

      PS: really appreciate the help

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s