Last/Max School Date of Each Month for X Axis?

[Maxwell Schulz]

Hi Everyone,

Hope you all are having a good day so far.

I am reaching out to see if anyone knows how to manipulate/create a calculated field for a Date field to have it show just the last (or Max day) for each month. I am trying to recreate the graph below which is from a google sheet using the data from the table in the second photo. The issue I'm running into is that while I can manipulate the date field to show month/year this only allows me to sum or avg TotalEnrollment for that entire month when what I actually want to show is the enrollment on the last CalendarDate aka school day of that month.  Any help or suggestions would be greatly appreciated and thanks for your time and assistance!

Max Schulz Director of Data Analytics Direct: 773-988-1051 |msc...@phalenacademies.org  Visit: PhalenAcademies.org| Enroll Your Scholar | Join Our Team Download Our Mobile App via the App Store or on Google Play

[Anthony Claypool]

I'm not likely the smartest person here to contribute to the answer, but I have been known to forcefit some solution between sheets and Looker/Data Studio.  😁

One wondering I have is how the data is collected in that sheet.  If it's entered manually, you could create another field in the sheet called "last date" and make it True/False (or 1/0 or some other dichotomous choice).  Then filter that visualization on that field.  

Alternatively, you could do something similar once the data comes over to looker to make a list of valid dates for last day of the month using a filter (CalendarDate = '2022-09-30' OR CalendarDate = '2022-10-29' and so on.  That'd be a pain to set up, but it should filter out only the results on the dates you care about and since those are discrete, finite and predictable for the year it should only be tedious and not totally inefficient.

Like I said, I'm sure someone else here might have a more elegant solution.  Best of luck!

Anthony

--
You received this message because you are subscribed to the Google Groups "Data Studio in Education" group.
To unsubscribe from this group and stop receiving emails from it, send an email to googleschool-data...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAEXsQdvzS%3DG1zggWp7-kAnHdhqRfLZ9uzkqYCuMQ8i2DScCBwQ%40mail.gmail.com.

--

Anthony Claypool Director of Data Systems & Analytics, Distinctive Schools (312)332-4998   www.distinctiveschools.org acla...@distinctiveschools.org 910 W. Van Buren Street, Suite 315, Chicago , IL 60607 IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.

[Laura Tilton *]

Hi Max, this is a GREAT question!! Like Anthony, I would probably try something in Sheets before I tried to do it in Looker Studio (mostly to avoid blending 🤣). BUT if you really needed/wanted to do it in Looker Studio, here's my suggestion: You could use blending to pull out only the enrollment on the max date of each year-month combo. 

In your data source (Resource > Manage added data sources), click the 3 dots and Duplicate the CalendarDate field, name it Year-Month CalendarDate, and change the type to Year-Month.

Then, under Resource > Manage Blends, you can blend your data source on itself to do what you're looking for as follows. Basically, it's a left join where you are ONLY looking to return the Max calendar date (on the left) for each Year-Month and Grade combination, and its associated TotalEnrollment on that date.

The resulting blended data source will have the following fields:

• Calendar Date
• gradelevel
• TotalEnrollment
• Year-Month Calendar Date

You can then use either CalendarDate or Year-Month CalendarDate as your dimension for your chart, depending how you want it to display.

I'm sure there are other ways to do this too that avoid blending, but this seems to work well!

Laura

--

You received this message because you are subscribed to the Google Groups "Data Studio in Education" group.
To unsubscribe from this group and stop receiving emails from it, send an email to googleschool-data...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAEXsQdvzS%3DG1zggWp7-kAnHdhqRfLZ9uzkqYCuMQ8i2DScCBwQ%40mail.gmail.com.

--

Laura Tilton

Education Data Professional

Twitter: @tiltondata

[Anthony Claypool]

See, I told you someone smarter would come along.  🤣 Great solution Laura!

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAO_um7tX2V5nYdy2Rk9pA6-9fM2TZ2Lrv8F%2BWbbr0EQB5kJ3Jw%40mail.gmail.com.

[Maxwell Schulz]

Dear Anthony and Laura,

Thank you both so much for your replies! I tried both out and got what I was looking for. Laura, your solution got me thinking that I could just do something similar in the query that the chart pulls from so I added maxdayofmonth and another join on schoolid and calendardate for it to my overall query (this actually provides the max instructional day of the month so I should probably rename). 

maxdayofmonth as(select schoolid, extract(month from CalendarDate), max(calendardate) as maxmonthcalendardate

FROM table1

group by 1,2),

So helpful to have this group to bounce ideas off of! Thanks again for time and assistance and hope everyone has a great weekend!

Max

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CA%2BD3xe%2Bz2SGTtFAnZj2hiddJfP8A8pkvhTD%3DpgnYNmoCw-fH7A%40mail.gmail.com.

--