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

[userpd_2021]

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

[rbartley · Accepted solution]

Hi,

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

See attached.

[userpd_2021]

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]

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

[rbartley]

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]

Hi @rbartley 

Below is the application.

[rbartley]

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.

[userpd_2021]

Hi @rbartley 

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

[rbartley]

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.  

[userpd_2021]

Thanks @rbartley  I will use this once.