Group by Date

Zack Wilson shared this question 14 months ago
Answered

Is it possible to get the data to group by the format of the date rather than list duplicate values (i.e. moving from date_id to month)?


/52579572a5b52f536f2261b716ab4118 >> /a0c71ca578aa95fb614e336caabf6883


Let me add... Is there an easier way than adding a "month-date" to the view...

Comments (8)

photo
1

Let me add... Is there an easier way than adding a "month-date" to the view...

photo
1

Hi Zack,

One easy way to do this is by creating a Date Function within the View that the Report is sourced from. You can do this by clicking the '+' in the bottom right corner of the left navigation pane, selecting the SQL Date or DateTime field and then the Date Function that you wish to apply.

In the first screenshot I have shown how you can do this with 'Month Name' and in the second screenshot I have a small report I made of Taxi trips taken by the Day of the Week.

Let me know if this helps!

Kind regards, Simon

photo
1

That is a nice work-around... But, anytime someone wants to group by a certain aspect of a date, I have to add it. I have tried Joining a "calendar" table (with varies formatted date values). Either way, they are not recognized as dates so it will not allow the Date Slider or the Unit Selection. The only way I see to do this is to actually have a date in the field. This is why I was wanting it to be a function of formatting the date. To expand, I am trying to get the function of a Date Slider and Unit Selection in the chart, with the compact data of the formatted date in the table.

photo
1

Hi Zack,


Thanks for clarifying a few things there for me, I understand what you are trying to achieve here.


I've played around with some data myself and I'm having similar difficulties, time series data and categorical data can be shown in the same report in the way that you want, with a date slider.


I think the solution here might be to think about what you want to achieve a little outside of the box. Have you considered using 'Drill Down' and/or 'Drill Through' to link date/time aspects of your report? How this might work is that you have the data organised hierarchically from lets say Year > Month > Week > Date > Timestamp and then your users are able to traverse through this and extract the information they want in the granularity they want, based on the aggregations of that particular metric.


For the above to work, it would be useful to know exactly what kind of structure you want the report to have. For example, which Report is will be the Parent - the Categorical Report or the Time Series Report? Is the more important aspect to be able to filter by Date, or by Month (or other Date Hierarchy)?


I haven't worked too much with Co-Displays and Drill Through Reports so I will play around with my own data and come back to you in a little with an example once I get a better understanding of your use case.


Kind regards,

Simon

photo
1

Hi Zack,


I've played around a little more and I think the solution to your question is to use a Dashboard. In Yellowfin 8, it seems that you are unable to link Co-Displayed reports on the Date Slider, this functionality is only available for Dashboards. In the Analytical Setup Settings of the Dashboard, you can toggle 'Link Slider' in your Parent Report and connect the Filter from that Report to the Child Report's Filter.


I've provided some screenshots that may help, and I'll quickly explain what I've done:

  1. Created a Time Series Parent Report based on date_id, this can measure absolutely anything. The important part is that the Date Slider will filter other Report(s)
  2. Created a Categorical Child Report, Drilling Down from Month > Week of the Year > Day of Week > Hour of Day for granularity. I then added a Filter based in the Time Series metric used in the Parent Report. In your case that would be date_id. This includes the metric that I am most interested in, in my case it was the average duration of a Taxi trip for that time period, e.g average duration of a taxi trip grouped by day of the week, or average duration of a taxi trip grouped by months of the year
  3. Created a Dashboard and added both reports, using the Time Series Report as the Parent and Linking the Date Slider to the date_id field in the Child Report

Now when you change the Date Slider for the Parent Report, that filters through to the Child Report(s) on your Dashboard.


I hope this helps get to what you are trying to achieve. Let me know if I can clarify anything.


Kind regards,

Simon

photo
1

That would work. I have done that on other reports/dashboards... It is not optimal as a dashboard (at least on v.8) is not as visually cohesive for a "simple" report. Thank you for looking into this. I tried to use drill down, but it doesn't work with the slider (I was trying to troubleshoot for another report writer). Thank you for the ideas. I believe this will help for the time being to get the information he needs.


"In Yellowfin 8, it seems that you are unable to link Co-Displayed reports on the Date Slider..." - is this available in v.9?

photo
1

Hi Zack,


Short answer is yes, it should be possible through 'Code Mode' as well as Dashboards. I haven't had a chance to play around much with Code Mode so I'm unsure exactly how this would look, but from my limited understanding you should be able to parse the Date Slider Values to a filter through Javascript for example. Otherwise, Dashboards have been revamped and the experience should greatly improve, especially for use cases like yours.


Let me know if there are any other questions I can help answer about this topic, otherwise, I will go ahead and mark this ticket as completed!


Kind regards,

Simon

photo
1

Hi Zack,


I'm going to go ahead and mark this question as answered. Please don't hesitate to leave a comment if you still require assistance with this question and I will get back to you shortly.


Kind regards,

Simon