Hi!
I have an imported data xlsx file where i have several columns: dates, developers and leads.
I need to group this data by weeks. Can you please help me: how can i do it?
Best regards,
Tanya
Hi!
I have an imported data xlsx file where i have several columns: dates, developers and leads.
I need to group this data by weeks. Can you please help me: how can i do it?
Best regards,
Tanya
Hi Tanya,
First convert your dates to weeks, then group the data, for example, if your dates are in yyyy-MM-dd format, use
GROUP( DATE_CONVERT( dates, "yyyy-MM-dd", "YYYY-WW" ) )
This will display the dates as week numbers. If you prefer to display dates, for example the first day of the week, you can then convert the weeks to a date value:
GROUP( DATEVALUE( DATE_STARTOF( DATE( dates, "yyyy-MM-dd"), week), "yyyy-MM-dd" ) )
For a complete list of date/time formats, please see Using Date and Time Formats.
Thank you,
Janice
Hi there,
Tried the suggestion above many times but something doesn't work, I am unsure where my mistake is.
I have the following date format: 08-01-2018
What I am looking for is that all dates from 07-01-2018 - 14-01-2018 are shown as 07-01-2018.
Would you mind helping me with this, I think I have tried as much as I could for myself.
Many thanks!
Hi Sebastian,
In your scenario, you could use (where the dates are in column A of your datasource):
DATE_STARTOF( DATE( A:A,"dd-MM-yyyy" ), week, 0, Sunday )
Then on the Properties tab, set Format to Date/Time, Input Format to Unix time (seconds) and Display Format to "dd-MM-yyyy",
Also, check your locale setting, if it is set to a European locale, your start of week will be Monday by default.
Thank you,
Janice
Hi Janice,
Thanks for the info. I have tried that and there are a few issues.
1. the word week and Sunday both appear in red and when hoving over it says, unrecognized keyword - does that matter?
2. would I use the formula in both, series and x-axis? I tried different combinations but it seems not to make a difference
3. the original date source details are displayed like this: 2018-01-08T16:57:43 , which I could use instead of the converted 08-01-2018. Again, I tried both but didn't get anywhere with it ;)
Could you please help me with this? I am not sure what I am doing wrong.
Many thanks,
Sebastian
Hi Sebastian,
The words 'week' and 'Sunday' must be selected from a picklist, not typed in as text strings. To do this, start typing (using the format in your original datasource)
DATE_STARTOF( DATE( A:A,"yyyy-MM-dd" ),
after you type the comma after DATE( A:A,"yyyy-MM-dd" ), the picklist will appear. Select 'week', then type ', 0,' and another picklist will appear. Select 'Sunday'.
The data in your series must align with your x-axis. The formula for your x-axis depends on what you want your x-axis to be, either everyday in current month or rolling 30-day window, etc. If you have further questions, please email support@klipfolio.com so our team can better address your specific scenario.
Thanks,
Janice
Hi there,
Hi Sebastian,
It is possible to start the week on any day of the week. You want to group your data from Thursday to Wednesday, correct? If this isn't working, please email support@klipfolio.com so our team can look more closely at your issue.
Thank you,
Janice
Hi,
Just a quick note that for anyone who happens upon this now, that there is a new, easier way to do this via our new Date/Time, and applied actions funcionality.
Now, all you need to do is change the output format, along with the dates grouping:
Warm regards,
Joshua
Hi there!
Is there a way we can display only the first day of the week (Monday) across the x-axis. e.g Historical data of the last 24 weeks will only show the dates that are Monday.
Best,
Geae
Hi Geae,
We have a number of date/time functions that can be used to manipulate dates as required. To return the first day of the week (where the week starts on a Monday) for a set of dates, use the DATE_STARTOF function:
DATE_STARTOF( dates, week, 0, Monday)
where 'week' and 'Monday' are selected from picklists and 'dates' points to your data.
Thanks,
Janice