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

[joeluqlik]

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

[sunny_talwar]

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()

[joeluqlik]

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

[sunny_talwar · Accepted solution]

Check attached

[joeluqlik]

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

[sunny_talwar]

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

[joeluqlik]

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      

[sunny_talwar]

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

[joeluqlik]

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

[joeluqlik]

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