Sorry if this has been address, but I could not find a similar situation. I have a date data item that shows one date from each week in each month. For instance:
03/06/2020 04/03/2020
03/13/2020 04/10/2020
03/20/2020 04/17/2020
03/27/2020 04/24/2020
I need to create a graph that only shows the last week of each month. In the above example it would be only 03/27/2020, 04/24/2020, .....
I tried a filter: [DATE] >= _last_of_month (DATE] - 6)
I get the following error: XQE-PLN-0101 The data types 'date' and smallint are incompatible in the subtract operator.
When I remove the '-6', I only get the weeks that include 30 or 31. Not all the last month dates include the very last of the month. Is there a better way to accomplish this?
I've only skimmed over this.
You might want to think about changing this:
_last_of_month (DATE] - 6)
to this:
( _last_of_month (DATE]) - 6 )
Quote from: Magdalina08 on 01 Feb 2021 05:31:48 PM
Sorry if this has been address, but I could not find a similar situation. I have a date data item that shows one date from each week in each month. For instance:
03/06/2020 04/03/2020
03/13/2020 04/10/2020
03/20/2020 04/17/2020
03/27/2020 04/24/2020
I need to create a graph that only shows the last week of each month. In the above example it would be only 03/27/2020, 04/24/2020, .....
I tried a filter: [DATE] >= _last_of_month (DATE] - 6)
I get the following error: XQE-PLN-0101 The data types 'date' and smallint are incompatible in the subtract operator.
When I remove the '-6', I only get the weeks that include 30 or 31. Not all the last month dates include the very last of the month. Is there a better way to accomplish this?
Hi,
The _last_of_month() function returns a full date, not just a day number. If you used it with today's date as an argument (today is 2021-02-01) you would get a result of 2021-02-28. The error is complaining that the result of the function is a date data type, and your calculation is trying to subtract an integer value from it, which isn't legal. Given that you want to compare a date with the result, you should use the _add_days() function with an argument of -6 to pinpoint the date 6 days before the end of the month.
How about using this as your filter:
[DATE] >= _add_days( _last_of_month ([DATE]), -6)
Does this give you what you need?
Cheers!
MF.
QuoteThe _last_of_month() function returns a full date, not just a day number. If you used it with today's date as an argument (today is 2021-02-01) you would get a result of 2021-02-28. The error is complaining that the result of the function is a date data type, and your calculation is trying to subtract an integer value from it, which isn't legal. Given that you want to compare a date with the result, you should use the _add_days() function with an argument of -6 to pinpoint the date 6 days before the end of the month.
How about using this as your filter:
[DATE] >= _add_days( _last_of_month ([DATE]), -6)
Does this give you what you need?
This semi works. It seems to be skipping months that have day values >= last of month - 6. Not sure why. It seems to only show every other month. Increments of 2. Perhaps there's a setting I'm missing on the graph now.
Update:
Your solution did work. I had to resize the chart to see every month available.