Challenge #87: Losing Daylight

The solution to last week's Challenge can be found HERE!

It's that time of year again....the leaves are starting to change color, the smell of pumpkin spice is in the air, and the amount of daylight for @JoeM's post-work bike rides is getting smaller and smaller. Just how much daylight did we lose this month? Take this week's Challenge to find out! Using the Input data provided, calculate the amount of daylight for each day in September, as well as the day-to-day differences in daylight and the total difference from September 1 to September 30.

*If you'd like to use a dataset for your location, you can download data on sunrise/sunset times from this site: https://www.timeanddate.com/sun/usa/ for US-based users.

challenge_87_start_file.yxmd

challenge_87_solution.yxmd

Parse

Preparation

Advanced

Data Analysis

Data Preparation

Basic

Accepted answers

All comments

jamielaird

I seem to be in a minority, but I love this time of year. Anyway, here's my solution:

Spoiler

challenge_87_JL.yxmd

David-Carnes

I, too, like Autumn, when the heat and humidity of Summer begin to subside.

Spoiler

David Carnes - 087.yxmd

MarqueeCrew

I wanted to calculate things quickly. I didn't constrain myself to the desired output format, but rather showed the difference between start and total time on a daily basis.

It is getting dark earlier, that's for sure. But there is a silver lining in that story. As you near the equator (get further from freezing weather), Summer days are shorter. In other words, there's another reason to want to be in Michigan. Of course, the opposite is true during the winter.

Cheers,

Mark

challenge_87_MarqueeCrew.yxmd

nick_ceneviva

Solution is attached. Loved that the challenge included adding a total row as this is something commonly asked on the discussion board.

Spoiler

challenge_87_Ceneviva.yxmd

NicoleJohnson

My solution! I modified the formatting of the end result a bit to show the am/pm for clarity on Sunrise/Sunset times and modified the Date column to include "Sep" in the date, rather than just the day of the month.

Spoiler

challenge_87_NicoleJohnson.yxmd

JoeM

@MarqueeCrew.... shhhhh - keep it down.... no one needs to know about Michigan summers.

patrick_digan

Spoiler

challenge_87_start_file.yxzp

Kenda

I was able to make use of some macros I had already worked on in the past. I used the given data and ran it again for Fort Wayne data. Pretty cool!

Spoiler

challenge_87_BarnesK.yxzp

Ukashi

Ok here we go - my first challange, Not sure how to hide my solution with neat "Spoiler" thing, so I'm attaching solution as file.

I've took sunrise and sunset data for Poland

challenge_87 solution.yxmd

ChristineB

Welcome, @Ukashi! Glad to have another Weekly Challenger join the fun!

lminors

Spoiler

Interested to see how everyone else approached this as there are endless ways to do it.

Thanks,

Luke - Keyrus UK

WC87_LMsolution.yxmd

Niklas

I went for a fairly spelled-out approach to make it well understandable. Interested in what others did.

Spoiler

challenge_87_NiklasE.yxmd

unknown

Attached are my solutions to my first Weekly Challenge. I created two solutions; the first uses the existing data set and the second uses sunrise/sunset data from three cities around the world, including one that is the southern hemisphere. As such, the days in Johannesburg are growing longer as ours are growing shorter. This is my first challenge and as such, I probably over-complicated the solutions. However, I had a fun time creating clean Data and DateTime fields.

Challenge_87_solution1.yxmd

Challenge_87_solution2.yxmd

unknown

I really like your spelled out approach. It was extremely easy to read and understand.

estherb47

Took a slightly different approach, and transposed the times into one column to parse and prep with one formula tool.

Spoiler

The trick to then calculating the length of daytime is to negate the sunrise, so that the cross tab tool is adding the negative sunrise and positive sunset. Joined back together.

Then my solution is like the others. Multi-Row, Union

samjohnson

Enjoyed the other solutions and I went a slightly different direction, using the Running Total tool to show the cumulative minutes lost through each day of the month! I also used the Text to Column tool with multiple separators to pull out the time. Great challenge.

challenge_87_start_file.yxmd

ChristineB

@Inactive User, congrats on completing your first Challenge! Glad to have you joining in the fun!

EmanueleE

I tried to solve it, without Summarize Tool or DateTime Tool.

Spoiler

#WC87-EE.yxzp

vishalgupta

challenge_87_Vishal.yxmd

elamm43

The first time I did it I used a reflexive join - it's much better with the Multi-Row Formula Tool! I'm glad the challenge helped me learn something new.

challenge_87_Lamm.yxmd

dricke

i think this works..

Spoiler

challenge_87_DR.yxdb

mceleavey

It's been awhile, what with work getting in the way.

Right, this is probably exactly the same as other solutions but here goes:

Spoiler

I first used the date parser to convert the sunrise and sunset times. I then used a datetimediff() to determine the length of the day. I then used a multi-row formula to determine the difference in day length from one day to the next, used a separate stream into a summary tool to determine the overall total of minutes different and unioned the streams. Fairly straightforward:

challenge_87_CM_Solution.yxmd

M-Shedid

Here goes to the first challenge :-) and hopefully many more to come

-->Updated file has the sum of difference, which was missing from the first

challenge_87_Shedid_Mod.yxmd

jdunkerley79

Working my way backwards in time ...

Spoiler

challenge_87_JD.yxmd

Natasha

Took me quite some time to figure out the proper syntax for the AM/PM identifier, as I did all the formatting in the formula tool.

Spoiler

challenge_87_Natasha.yxmd

JonM

My solution is not as elegant as the majority that are here, however it should be dynamic enough to work fine with the next months data specifically changing the September Total to October Total.

Challenge_87.yxzp

LordNeilLord

A nice one to get the brain working, I wanted the datetime tool to be more flexible in accepting am|pm and outputting as 24hr...sadly it was not therefore more work for me! @Ned can this be a thing?

Spoiler

challenge_87_LNL.yxmd

mceleavey

Not sure if this is relevant, but there's a conversion for a 24 hour clock format which is datetimeformat(<field>,"%H"). This should give you 00 to 23 hours.

palessio

Spoiler

I converted everything to Alteryx Date format, so that we can also explore the result set in other tools (e.g. Tableau)

challange_87_solution_file_Piero_Alessio.yxmd

SeanAdams

Very similar to the other solutions provided

Spoiler

challenge_87_SeanSolution.yxmd

robcarroll

All done.

challenge_87_solution.yxmd

stubartmess

Farewell again sun, see you next year!

challenge_87.yxmd

derekbelyea

Not as elegant as other solutions but got the job done.

Analytix Studio with Lens Logo.png

challenge_87_Solution_Derek_Belyea.yxmd

dominiklz

Spoiler

challenge_87_Dominik.yxmd

BenMoss

Love me some parsing.

Taken a leaf out of @BenG's best practice book by splitting out my formulas too. Much better for other users viewing the workflow.

Spoiler

challenge_87_start_file - BM.yxmd

BenG

Love it!!

TerryT

That Formula tool does so much!

TTT-challenge_87.yxmd

PeteV

I decided to use the Running Total tool for the Month of September Total Difference. I thought it provides a different view of the total difference on a daily basis.