Extract Max date from Date

[Magdalina08]

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?

[bus_pass_man]

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 )

[MFGF]

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.

[Magdalina08]

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?

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.