[Resolved] Filter by custom date field to find posts from within the current month

[Author]

Posts

[gavinS]

I am trying to sum values from posts with a custom date field within the current month, but I'm struggling to find how to filter by current month.

This is my loop output:

[wpv-layout-start]
	[wpv-items-found]
     
	<!-- wpv-loop-start -->
		<wpv-loop>
          [add-to-total]
          [types field="potential-value" output="raw"][/types]
          [/add-to-total]
         
	    </wpv-loop>
     R [show-total]
	<!-- wpv-loop-end -->
	[/wpv-items-found]
	[wpv-no-items-found]
		[wpml-string context="wpv-views"]0[/wpml-string]
	[/wpv-no-items-found]
[wpv-layout-end]

I use a shortcode to sum the values and then display them after the loop. This works fine if I don't try filter by date, I get the correct sum of all values. But as soon as I try to filter I get a result of '0'.

How do I filter a custom date field to only sum the values within the current month?

I have tried filtering by:

Select posts with custom field:
Start Date is a number equal to THIS_MONTH()

I have tried:

Select posts with custom field:
Start Date is a number in THIS_MONTH()

I have tried:

Select posts with custom field:
Start Date is a date in THIS_MONTH()

And I have tried:

Select posts with custom field:
Start Date is a date equal to THIS_MONTH()

None of these seem to work. I just get a value of 0 every time. Although I know I have posts with a date in this month and a value to sum. What am I doing wrong?

[Shane Supporter Languages: English (English ) Timezone: America/Jamaica (GMT-05:00)]

Hi Gavin,

Thank you for contacting our support forum.

This a strange issue as the filters should work when you add them.

Would you mind providing me with temporary admin access for the website to check on the view for you ?

I'll enable the view debugging so I can analyze the query to see if the information is being pulled correctly.

The private fields are enabled for your next response.

Thanks,
Shane

[Shane Supporter Languages: English (English ) Timezone: America/Jamaica (GMT-05:00)]

Hi Gavin,

I was not able to get this filter to work 🙁

So what I did was to ask for assistance from our 2nd Tier supporters.

They should understand better how these filters work.

Hopefully we can hear from them soon so we can resolve this issue for you 🙂

Thanks,
Shane

[gavinS]

Any news there?

[gavinS]

Hello? Anyone there?

[Adriano]

Hello,

In order to achieve what you want you need to add a filter by custom date field which date between THIS_MONTH and FUTURE_MONTH.

Please let me know if you are satisfied with my reply and any other questions you may have.

Regards,

Adriano Ferreira

[gavinS]

Hi Adriano

Will that not also include the values from the posts in the next month?

I only want to include the values from the current month.

Thanks

Gavin

[Adriano]

So date between THIS_MONTH and FUTURE_MONTH(1), this will be from 216-02-01 to 2016-02-29.

Please let me know if you are satisfied with my reply and any other questions you may have.

Regards,

Adriano Ferreira

[gavinS]

Thanks Adriano

That seems to be working.

Now I'm trying to sum the values for the next month (March), but it doesn't seem to be working.

I'm trying to sum the values with the following filter:

Select posts with custom field:
Start Date is a number between FUTURE_MONTH(1), FUTURE_MONTH(2)

But this seems to also be including the values from April. What am I doing wrong?

[Adriano]

What is this page? I will need to enable Views debugger to see the query that is being called.

[gavinS]

Hi Adriano

You will need to be logged in to view. Please send me private message details and I'll give you a login.

It is not on the same site as the private details above. I have since moved it over to another URL.

[Adriano]

Ok, sent.

[gavinS]

Hi Adriano

Sorry about the delay. I'm having a problem with my backup plugin, due to some php extension that is not installed, I'm waiting for my host to sort that out.

In the meantime I have discovered what the problem is. My filter is set to:

Select posts with custom field:
Start Date is a number between FUTURE_MONTH(1), FUTURE_MONTH(2)

What it is doing is including the post from 1 April. If I set the post start date to 2 April then it doesn't include it.

Is there any way to filter it so the date is greater than or equal to FUTURE_MONTH(1) AND less than FUTURE_MONTH(2) so it doesn't include the posts made on 1 April? I tried to add both of those filters, but it seems to only allow one filter per custom value.

[Adriano]

I think it will be easier by using our internal API:

add_filter('wpv_filter_query', 'posts_within_current_month', 10, 2);
function posts_end_next_week($query, $settings) {
    if($settings['view_id'] = 123)
    {	
    	$query['meta_query'] = array(
			array(
				'key' => 'start_date',
				'value'   => array( strtotime(date('Y-m-01')), strtotime(date('Y-m-01'))),
				'type'    => 'date',
				'compare' => 'BETWEEN',
			),
		),
    }
    return $query;
}

Replace start_date with your custom field slug and view_id with the ID of the View.

Please let me know if you are satisfied with my reply and any other questions you may have.

Regards,

Adriano Ferreira

[gavinS]

Hi Adriano

Thanks, I think I almost understand what this is doing. Please correct me if I'm wrong.

This filter is applied to the view so I can choose which dates should be used yes?

I notice that the add-filter mentions the following function: 'posts_within_current_month'

But the function below is called 'posts_end_next_week'. I'm guessing these should be the same?

Also I'm not completely clear on what the two strtotime(date('Y-m-01')) in the value array are. Is this where I should be setting the two dates that I want to filter between?

So, I want to go from the 1st of the next month (ie March if I called it today), to the 31st of next month, can you tell me how I would write that?