Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am having problems with loading a date (5/31/2017). ReportDate is the field which is shared between the two tables, but 2 date values keep showing for 5/31/2017. It is like QV is recognizing one of the dates as something other than a date.
I am using these expressions to create the date fields:
Table 1:
Date(ReportDate,'M/D/YYYY') as ReportDate
Table 2:
Date(MonthEnd(AddMonths(P_ReportDate,-11)),'M/D/YYYY') AS ReportDate
The field shows 2 dates for 5/31/2017...
How can I fix this?
Seems that you are not loading a date.
Did you try to load the date-field with DATE#(), e.g.
DATE(DATE#(Field2Read, 'M/D/YYYY'), 'YYYYMMDD')
So the inner would ask QV to read the field in the given format M/D/YYYY (or whatever it is). This will result technically in a number. The outer format then will ask QV to show this number as a date in the format YYYYMMDD (or any other, as per your choice).
without seeing the real data, you may ensure that both dates have the same data-type (in this case a date without decimals (= hours)).
Table 1:
Date(FLOOR(ReportDate),'M/D/YYYY') as ReportDate
Table 2:
Date(MonthEnd(AddMonths(FLOOR(P_ReportDate),-11)),'M/D/YYYY') AS ReportDate
may do the trick already.
Else you may have to check the import-formats and use DATE# in order to force the reading in a different format than your common format.
Peter
Hi Peter,
Thank you, but when I add the FLOOR function the date disappears altogether...
Seems that you are not loading a date.
Did you try to load the date-field with DATE#(), e.g.
DATE(DATE#(Field2Read, 'M/D/YYYY'), 'YYYYMMDD')
So the inner would ask QV to read the field in the given format M/D/YYYY (or whatever it is). This will result technically in a number. The outer format then will ask QV to show this number as a date in the format YYYYMMDD (or any other, as per your choice).
Thank you. I ended up using a variation of the two and got it to work.