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: 
userpd_2021
Contributor III
Contributor III

How can I compare my previous data in pivot table to my current data if my previous data is null?

Hi Team,

How can I compare my previous data in pivot table to my current data if my previous data is null?

                         q1                    q2               q3          q4

12                    -                     a                    a            b

13                   a                  b                       -             b

14                  a                   a                 a                   a

 

In the above example while I am using before function to give some color conditions

if( qvalue<> before(qvalue),'Yellow') This condition  doesn't get satisfy when there's a null value in between 

Row 13 Q4 color should change to yellow which is not the case.

How can this null be handled to get proper output?

#Experts

Labels (3)
1 Solution

Accepted Solutions
rbartley
Specialist II
Specialist II

Hi,

It seems to work ok for me: if(ColumnNo()>1, if(MinString(Value) <> Before(Value),yellow())) 

rbartley_0-1641824005695.png

See attached.

View solution in original post

9 Replies
rbartley
Specialist II
Specialist II

Hi,

It seems to work ok for me: if(ColumnNo()>1, if(MinString(Value) <> Before(Value),yellow())) 

rbartley_0-1641824005695.png

See attached.

userpd_2021
Contributor III
Contributor III
Author

Hi Rbartley,

Thanks for the response.

I used the same expression but the q1 is getting highlighted and the value after null is not getting highlighted.

What can be the reason?

I am using 4 dimensions in the pivot table.

userpd_2021_0-1641877788238.png

 

userpd_2021
Contributor III
Contributor III
Author

Hi @rbartley ,

I have  fully expanded the pivot table , Presentation--->fully Expanded

How can I apply the same logic on the expanded fields.? to get the output?

With fully expanded I am not getting the output

userpd_2021_0-1641880631777.png

 

rbartley
Specialist II
Specialist II

Hi @userpd_2021 ,

Could you please post your app so that I can make sure I'm working with the same data and structure.

userpd_2021
Contributor III
Contributor III
Author

Hi @rbartley 

Below is the application.

 

rbartley
Specialist II
Specialist II

Hi @userpd_2021 ,

 

Ok, I see the issue. You have no record at all for Sub text 13a for q3.  This is not the same as saying that the value field has a null value, the data simply does not exist.  As such, Before(Value) evaluates to "a", not null, so there appears to be no difference between the value for q2 and q4.  The simplest way (and perhaps the only way) of resolving this would be to update your source file or your load script to include these missing records.

 

rbartley_0-1641893851978.png

 

 

userpd_2021
Contributor III
Contributor III
Author

Hi @rbartley 

Thank you for the help I will try to populate the missing data in the script. 

rbartley
Specialist II
Specialist II

Just thought of another solution, which would appear to fit your scenario.

Try this expression in the Background color expression property:

if(ColumnNo()>1,if(MinString(Value) <> Before(Value),yellow(), if(Num(Right(Q,1))-Num(Before(Right(Q,1)))<>1,green())) )

As well as comparing the value with the previous value, it also compares the quarter number with the previous one and, if the difference is not 1, colours the cell green.  

rbartley_0-1641911107606.png

 

 

userpd_2021
Contributor III
Contributor III
Author

Thanks @rbartley  I will use this once.