0

I am creating a view to breakdown the number of node published per week using views. I were able to create a view that will show me the breakdown day or month but there is no option for week. How will I do that?

enter image description here

and the result

enter image description here Also how do I make it show the first day of that week or the first item publish for that week.

Any help is appreciated.

** update**

I were able to get the right mysql query:

SELECT COUNT(DISTINCT node.nid) AS nid, MIN(node.nid) AS nid_1, 'node' AS field_data_field_published_at_node_entity_type, YEARWEEK(DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL -14400 SECOND), '%Y%m%d')) AS node_created_yearweek FROM node LEFT JOIN field_data_field_published_at field_data_field_published_at ON node.nid = field_data_field_published_at.entity_id AND field_data_field_published_at.entity_type = 'node' WHERE (( (node.status = '1') )) GROUP BY field_data_field_published_at_node_entity_type, node_created_yearweek ORDER BY node_created_yearweek DESC LIMIT 55;   

How can I add the query to the view?

update 2 base on you answer it is grouping by day showing week:
enter image description here

enter image description here

update 3
enter image description here

and the list is still the same grouping by day showing week.
The numbers in parenthesis are the number of item published that/a day of the week.
Example on week 21 we published item 6 days in that week and in the parenthesis is the number of item published for each day.

enter image description here

upate 4 some progress
removed sort criteria enter image description here

expected output but all years (last 3 years) are combine
enter image description here

3

Contextual Views Summary

You can show weekly results using a Contextual filter.

In D7 you can add a new Contextual filter Content: Created week and configure it with these options:
When the filter value is NOT available
Display a summary
Sort by Date
Format List
Base path (you must enter the Page path if this isn't the first Page Display in your Views)
Display record count with link
Override number of items to display to 0 to have an infinite number of weeks show. Use this if you want the Pager to control the number of nodes shown for each week (if there are many). Otherwise, the Pager will apply to the number of weeks as well as nodes!

This way you get a list of weeks by their number, each week links through to a list of nodes created during that week:

Views Summary by Week

The Sorting criteria you add to the Views will affect the list of nodes you see after you click through a summary week link. If you add the Content: post date criteria, the nodes will show as the oldest first if you set it to ascending.


Aggregation

For more control, you can use Aggregation to count the results, but it involves a lot more steps to recreate the Summary like above.

After you turn on Aggregation simply change one of the Fields that is unique (for example Node ID) to Count DISTINCT and it will show the number of results.

You'd have to combine it somehow with the Contextual weekly filter if you wanted to display something different from the Summary, possibly create one Views display for the first node of the week, then in an Attachment do the counting and attach it to the week.

Adding a set query to Views is a bit of an overkill since the purpose of Views is to build queries (through an interface).

  • thanks for the answer I must be doing something wrong it is grouping by day, see image plz. – mamesaye Sep 22 '18 at 22:14
  • Why do you think it's by day? I think it may be something to do with the Paging. What happens when you set the Views Pager to all items? Can you post the Contextual filter settings as well? (You can make the browser window narrower before taking a screenshot so it's a smaller image.) – prkos Sep 23 '18 at 9:44
  • pager is set to 0 in the view pager and in the Override number of items to display to 0. see image for Contextual filter. Thanks – mamesaye Sep 23 '18 at 23:06
  • This is strange behavior, there must be something out of place, I've never seen the Summary behave that way. What happens when you enter the Base path in Contextual (weekly-count)? If you have other pages in that View before this page it won't work as expected. I don't expect Format Grouping would affect this but do you have it enabled? What is the Published at Field about, it has nodes repeated with different dates in the results? Shouldn't it be Post date? The Contextual Created week is using the Post date information, and not any custom field you created. – prkos Sep 24 '18 at 10:54
  • 1
    There isn't one special Contextual filter that combines years and weeks (like it does in Content: Created year + month) but you can use the Content: Created year in combination with Content: Created week, just rearrange them so the year one comes first. Then you will first have to choose the year, after that the week. The week counts will now be accurate, per year only. Maybe it wouldn't be difficult to create a custom Created year + week filter if you take a look at the Created year + month. BTW I didn't have any problems with the Content: Post date sorting filter. – prkos Sep 24 '18 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.