I have some frustrating sales data with two separate date fields. This doesn't apply to all data, just a small percentage.
Certain products come through with either 00000000 for a date in the one field and the end of the month in the other.
So for example, I have a bunch of sales with a date of 1/31/2017 (with zeroes in the other date field).
Since I don't have a better way to "place" these sales, I want to give them a date of the beginning of the month. Once the month is over, however, I am fine with the date reverting back to the end of the month.
I tried the following:
if [ZPOST_DATE]>=[Today] then date([Today]-(day([Today]-1)))
ELSE
[ZPOST_DATE]
END
Where [ZPOST_DATE] is the date field that is the last day of the month for these particular sales and [Today] is a parameter.
I can't really explain why the above doesn't work. Almost all of my data gets lumped into nulls.
The data source is an SAP HANA View so some date functionality does not work. I am on version 9.2.
I tried this and it appeared to be working but now I am trying to go a little further and make ANY date that has null in one field and make it the beginning of the month.
Here is the formula I created:
DATE(if isnull([ZINVOICEDATE]) then DATETRUNC('month',[ZPOST_DATE]) else [ZPOST_DATE] END)
Where [ZINVOICEDATE] is null when [ZPOST_DATE] is the end of the month. For some reason this isn't working properly. Here is some data and I'm not sure why it is not. Any ideas?
