Compare two dates in a pivot table

[Anonymous]

Hi Everyone,

I am trying to compare two dates in a pivot table. Both dates are 'DUE_DATE' pulled from the same table in the database. I have included a screen shot of the two dates I am comparing, highlighted and labeled. I have hidden some of the items to protect the innocent [:D]. As you will see, Date2 is only 3 months away from Date1. This is an example of items/cells I want to highlight. The two dates should be 6 months apart, and this will help the person reviewing this data in order to correct the database.

Here is what I want to accomplish, or what I think needs to be done:
If Date1 is greater than Date2 + 6 months, highlight as red.
If Date1 is less than Date2 + 6 months, highlight as red.

I tried comparing the two columns using relational operator > & <, but nothing happens. Would this have anything to do with the fact that I have other data in the columns, Month(s) fields?
The different combinations I have tried:
- If(Column(17) > Column(21) + 6, rgb(255,173,173))
- If(Column(17) > (Column(21) + 6), rgb(255,173,173))
- If(Column(17) > Column(21) and Column(21) + 6, rgb(255,173,173))
- If(Column(17) > Column(21) or Column(21) + 6, rgb(255,173,173))
- If(Column(17) > Column(21) and (Column(21) + 6), rgb(255,173,173))
- If(Column(17) > Column(21) or (Column(21) + 6), rgb(255,173,173))

Any help would be greatly appreciated. Thank you!

Mike

[#2]

Mike

instead of + 6 try using the add months function and if you can upload some dummy application then it will be easy to understand

Talha

[Anonymous]

Hi Talha,

I have tried your suggestion, but I am having no luck with it. No cells are being highlighted. Do you know how I would upload a dummy qvw to the forum? I have included code below from a test qvw I created and a screen shot of the Excel test data.

Here is the script code:

LOAD Name,
 Test,
 Code,
 [Hire Month],
 [Due Date],
 Scheduled
FROM  (biff, embedded labels, table is [Sheet1$]);

Here is the 'background color' code for Due Date:

=If(Column(1) > addmonths(Column(3),6),rgb(255,173,173),
 If(Column(1) < addmonths(Column(3),6),rgb(255,173,173),
 If(Column(3) > addmonths(Column(1),6),rgb(255,173,173),
 If(Column(3) < addmonths(Column(1),6),rgb(255,173,173)))))

Thanks,

Mike

[#4]

Mike when you reply this back on the community then there is ,on the top of this page 3 tabs compose,options and preview, go into options and there you can select the dummy qvw you want to post on this community,it will get attached to your message.

Try to send the dummy qvw file

Talha

[Anonymous]

Let's see if this works.

[Anonymous]

And now the excel ss.

[#7]

I think you are looking for something like this i have not spend time on the if statement

Please see

Talha

[#8 · Accepted solution]

Hi Mike,

I think you are looking for something like this, which compares the 2 Due Dates.

[Anonymous]

Talha,

Sorry for the late reply, but this is still highlighting all fields cells for Due Date. I have tried different things with addmonths and nothing seems to work with that function.

The next response from Xena works and is what I was looking for. Thank you for your time!

Thanks,

Mike

[Anonymous]

Xena,

Yes, this is what I am looking for. I appreciate your help very much!! Thank you!!

Mike