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

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

Hi,

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!

 Employee Week Title Manager Emp 1 1 Sr Engineer Tom Emp 1 2 Sr Engineer Tom Emp 1 3 Engineer Manager Tom Emp 1 4 Engineer Manager Jim Emp 1 5 Engineer Manager Jim Emp 2 1 Accountant Betty Emp 2 2 Accountant Betty Emp 2 3 Accountant John Emp 2 4 Manager John Emp 2 5 Manager John

JL

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

You should be able to use Above() and Below() functions to do this

Missing Manual - Above() and Below()

In case this is in a pivot table where week is a pivoted dimension, then you should be able to use Missing Manual - Before() and After()

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

Hi Sunny,

Thanks for the formula.  It seems to work, but don't know how to further enhance it to work properly for my case.

Do you mind taking a look at the attached qvw file?  The goals are summed up within the file's text box on the right.

Really appreciate your help in this, thanks!

JL

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

Check attached

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

Hi Sunny,

Thanks for the quick turnaround, really appreciate your help here.

I think that looks like it works.

Just a quick question to confirm the expression on how it works:

=Aggr(if(Title<>Above(Title) and RowNo() > 1,lightgray()), [Position ID], Data)

So we a

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

I think you left your post incomplete... not sure what you question is

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

Hi Sunny,

The chat kept kicking me out once I used the space bar or some characters on my keyboard last night so I couldn't finish my comment/question.  So is the formula basically saying that do the comparison of the data with the one above it only at the aggregate levels of Position ID and Data dimensions?  and don't compare the line one with the header row which will always yield the difference, right?

I actually gave it more thoughts and thinking of adding couple more enhancements to it:

1)  To show the weeks across so to lessen the number of lines.  So for each dimension there will be only one line needed.  Of course I will need to have 3 lines (Title, Manager, Amount) for each Position ID going down but the  weekly comparison of these 3 lines are going across in columns.  I tried changing it to Pivot table, but not stacking the data correctly.

2)  Lastly is to show lines that are with changes only between the columns.  By having the comparisons across, we can see the difference versus the value in the period before all on one line.  If any line has no changes in the period selected, do't show the line.

Thanks!

JL

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

You are correct about what the expression is doing. For enhancements, do you mind sharing what you have in mind as an expected output?

• ###### Re: How to highlight changes in a dimension when one row's value is different then the value of the row right before?
 Position ID Type 20170901 20170908 20170915 D3183315N Title 1033 1044 1044 D3183315N Manager 1144 1144 1144 D3183315N Var Amt 3000 3000 3500 D3185888N Title 1079 1079 1021 D3185888N Manager 1024 1024 1033 D3185888N Var Amt 4000 5000 5000

I was thinking to have the table layout like the one above with the weeks going across upon the user's choosing from a list box of the week (or the Data field).  In this case the user chose 3 weeks.

In this case, there are two persons here with their Position ID going down, and each person has 3 lines of Type (Title, Manager, and Var Amt) being compared across the 3 weeks.

Where there is change comparing to the week before, they are highlighted in color (I am using bold face and underlined here since I can't color this table in this chat).  For example, as in line 1 the 1044 in the middle week is a changed so should be highlighted, no change in row 2, row 3 has change to 3500 in week 3 (20170915).

Can this be done?  or we have to layout the data differently?

The last thing I am trying to do is to not show row 2 as a result since it has no change for the Manager field for all 3 weeks.

Thanks!

JL

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

With this view of the weeks (the Data field) going down, and with the columns (Title, Manager, Amount) being compared across, is there a way to highlight the person (using the Position ID field) if any of the 3 columns being compared had a change and was highlighted in gray?

Is there a way to say if on this row the Title is highlighted in gray because it is different than the prior row then highlight the person?

That way we can filter out to show only person or rows that have changes.  Will be great to be able to do this filtering in Qlikview, otherwise can filter for that in Excel.

Thanks!

JL