Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joeluqlik
Contributor III
Contributor III

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!


EmployeeWeekTitleManager
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

JL

1 Solution

Accepted Solutions
sunny_talwar

9 Replies
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
Contributor III
Contributor III
Author

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

Check attached

joeluqlik
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

Position IDType201709012017090820170915
D3183315NTitle103310441044
D3183315NManager114411441144
D3183315NVar Amt300030003500
D3185888NTitle107910791021
D3185888NManager102410241033
D3185888NVar Amt400050005000

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
Contributor III
Contributor III
Author

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