topic Re: How can I compare my previous data in pivot table to my current data if my previous data is null? in App Development

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

userpd_2021 — Mon, 10 Jan 2022 11:13:20 GMT

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

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

rbartley — Mon, 10 Jan 2022 14:16:21 GMT

Hi,

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

See attached.

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

userpd_2021 — Tue, 11 Jan 2022 05:10:30 GMT

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.

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

userpd_2021 — Tue, 11 Jan 2022 05:57:49 GMT

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

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

rbartley — Tue, 11 Jan 2022 08:31:00 GMT

Hi @userpd_2021 ,

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

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

userpd_2021 — Tue, 11 Jan 2022 09:19:06 GMT

Hi @rbartley

Below is the application.

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

rbartley — Tue, 11 Jan 2022 09:46:44 GMT

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.

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

userpd_2021 — Tue, 11 Jan 2022 11:17:44 GMT

Hi @rbartley

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

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

rbartley — Tue, 11 Jan 2022 14:26:09 GMT

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.

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

userpd_2021 — Wed, 12 Jan 2022 04:54:47 GMT

Thanks @rbartley I will use this once.