topic Compare two dates in a pivot table in QlikView

Compare two dates in a pivot table

Anonymous — Thu, 15 Oct 2009 22:43:35 GMT

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

Compare two dates in a pivot table

Thu, 15 Oct 2009 23:12:06 GMT

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

Compare two dates in a pivot table

Anonymous — Fri, 16 Oct 2009 21:44:44 GMT

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

Compare two dates in a pivot table

Fri, 16 Oct 2009 23:20:35 GMT

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

Compare two dates in a pivot table

Anonymous — Fri, 16 Oct 2009 23:25:35 GMT

Let's see if this works.

Compare two dates in a pivot table

Anonymous — Fri, 16 Oct 2009 23:26:40 GMT

And now the excel ss.

Compare two dates in a pivot table

Fri, 16 Oct 2009 23:55:12 GMT

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

Please see

Talha

Compare two dates in a pivot table

Sun, 18 Oct 2009 17:22:33 GMT

Hi Mike,

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

Compare two dates in a pivot table

Anonymous — Thu, 29 Oct 2009 22:25:59 GMT

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

Compare two dates in a pivot table

Anonymous — Thu, 29 Oct 2009 22:27:03 GMT

Xena,

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

Mike