9 Replies Latest reply: Nov 27, 2017 4:34 PM by Joe Lu RSS

    How to highlight changes in a dimension when one row's value is different then the value of the row right before?

    Joe Lu



      I have many weeks of HR data, as an example in the Excel list below within the Week dimension (say numbering from 1 to 40 weeks), and I want to find a way to compare the week of one line to the other week of another row right before, and highlight the change if this week is different.


      The user may not always select a bunch of consecutive weeks, for example the user may choose from the list box weeks 3,6,9,12, and the formula will still compare in that order of 6 v 3 to see any change, then 9 to 6, then 12 to 9, etc.


      In this example, within the Title column, Emp 1's Week 3 has a change from Sr Engineer to Engineer Manager, so that cell should be highlighted, same with Emp 2's Week 4 when the Title change from Accountant to Manager.


      In the Manager field, Emp 1's Week 4 value should be highlighted since in changed from Tom to Jim, same with Emp 2's Week 3 value where it changed from Betty to John.


      I try to do this for multiple fields in a straight table in Qlikview, thanks!

      Emp 11Sr EngineerTom
      Emp 12Sr EngineerTom
      Emp 13Engineer ManagerTom
      Emp 14Engineer ManagerJim
      Emp 15Engineer ManagerJim
      Emp 21AccountantBetty
      Emp 22AccountantBetty
      Emp 23AccountantJohn
      Emp 24ManagerJohn
      Emp 25ManagerJohn