Calendar/date issue - please help!

[alexis]

Hi

I have attached a QVW that has a simplified view of my problem.

The QVW has a data file "Welfare payments" and a "Calendar" which I generate to offer rich calendar-type functionality

The 2 tables are linked by a field called "PaymentDate"

For some reason, none of the selections from a Calendar field take me to the relevant records. This is driving me insane. Any suggestions.

Regards and thanks in advance

Alexis

[giakoum]

Καλησπέρα Αλέξη.

υπάρχει περίπτωση να έχουμε κανένα ελληνικό Μ ή Υ στο 'D/M/YYYY'?

[alexis]

Good suggestion Ioanni.

In fact,to make sure that there are no Greek characters in the 'D/M/YYYY' I changed the code slightly to make sure that we are formatting the raw data:

WelfarePayments:

LOAD WPayIdentity,

     WName,

     WPayAddress,

     WPaymentDate,

     Date(WPaymentDate, 'D/M/YYYY') As PaymentDate, 

     WRunDate,

     WFormattedPaymentDate,

//     Date(WFormattedPaymentDate, 'D/M/YYYY') As PaymentDate, 

     WAmount,

     CATEGORY,

     WPaymentId,

     WNationality,

     WComment,

     WGrantCode

FROM

QVD\WelfarePaymentsCurrentSAMPLE.qvd

(qvd);

[giakoum]

Month(WFormattedPaymentDate) and month(PaymentDate) match exactly and work as a filter quite nicely, so there must be a mistake in the expression of month in the calendar.

If you take Month (from calendar) in a straight table, it shows null, while the above expressions show month name just the same.

Try, instead of selecting M AS Month in the calendar, to select month(D) AS Month. I know it is done already in the DateIsland but maybe it helps.

[alexis]

Ioannis Giakoumakis wrote:

Month(WFormattedPaymentDate) and month(PaymentDate) match exactly and work as a filter quite nicely, so there must be a mistake in the expression of month in the calendar.

If you take Month (from calendar) in a straight table, it shows null, while the above expressions show month name just the same.

Try, instead of selecting M AS Month in the calendar, to select month(D) AS Month. I know it is done already in the DateIsland but maybe it helps.

Hi Ioannis

Yes I am aware that Month(WFormatedPaymentDate) and month(PaymentDate) match exactly - that was the purpose of my demo application; this is not surprising as both of these fields, in essence, reside in the same table (the payments table). What buffles me is the fact that the PaymentDates of the Calendar and Payments appear to not be the same (although they look the same)...

[giakoum]

Hi Alexis

PaymentDates are the same, at least in the sample you posted, but month, as created in the calendar is not the same. I think that the month creation is the problem, but cannot test it as I cannot reload.

Είσαι ακόμα Κύπρο? Ελπίζω να είσαι καλά!

[rwunderlich · Accepted solution]

WFormattedPaymentDate has a time portion in it, which you are not removing, so the PaymentDate in the WelfarePayments retains the time portion and does not match the Calendar table.

The Date() function is a formatting function, it does not remove the time. Add a floor() function to fix.

Date(floor(WFormattedPaymentDate), 'D/M/YYYY') As PaymentDate

-Rob

http://robwunderlich.com

[alexis]

Hi all,

I have 2 QVW applications, one that acts as QVD generator

Still in Cyprus trying to keep my head above water!

The idea of generating the calendar and linking it with the payments data  is to benefit from ALL the fields that I have built in the feature-rich calendar, so it is imperrative that the 2 tables are properly linked. I will try and attach the QVD so that you can re-run it.

Much appreciate your help mate!

[giakoum]

yeap, I have to admit, there is no one like Rob...

Alexis, if you replace your expression with Robs, it works like a charm!

I have tested and reloaded it.

Thank you Rob, excellent!

[alexis]

Hi Rob

That IS the correct answer and I thank you for that.

How could you tell that the "WFormattedPaymentDate" had a time element to it - just by prevewing the table shows it as d/m/y - the only clue that I can see is that "WpaymentDate" (which is the raw version of "WFormattedPaymentDate") is shown as 40960,12345 (which I am guessing the integer part is the date part and the decimal part the time)?

Thanx again

Alexis