How to group by month (MMM-yy)
13 November, 2013
Hi,
We would like to sort month names and day of the week names in a report. The only sorting I can see is alphabetical. How can we sort then into valid month and weekday orders?
thanks in advnace,
Venkataratnum
We would like to sort month names and day of the week names in a report. The only sorting I can see is alphabetical. How can we sort then into valid month and weekday orders?
thanks in advnace,
Venkataratnum
Hi Venkataratnum,
Please use Org Ref functionality to do a custom sort, there is a good explanation of it in this Yellowfin Community News article.
regards,
Dave
Please use Org Ref functionality to do a custom sort, there is a good explanation of it in this Yellowfin Community News article.
regards,
Dave
Hi Dave,
We do have a Month column that includes the year. That way it is distinct without needing to include the year in the report. How do I order that column? If I used the Org Ref method you mentioned then I would have to order all months over all years. We have Dates of Birth so this would be very time consuming.
e.g.
November-11
December-11
January-12
February-12
March-12
thanks in advnace,
Venkataratnum
We do have a Month column that includes the year. That way it is distinct without needing to include the year in the report. How do I order that column? If I used the Org Ref method you mentioned then I would have to order all months over all years. We have Dates of Birth so this would be very time consuming.
e.g.
November-11
December-11
January-12
February-12
March-12
thanks in advnace,
Venkataratnum
What type of DB are you using?
Try the following approach:
Go to your View. Step 2 (View Fields). On the left-hand side panel locate the directory called �Calculated Fields�. Look inside the sub directory called �Date Hierarchy. You should have a Month Start Date function. Drag and drop it to your Available Fields area.
Make sure it is a dimension. Specify a Date Field you would like apply your function against (i.e. it can be Date of Birth). Please refer to the screen shots below:
Then click on the second tab called �Format� and apply your preferred formatting.
Save and activate your new dimension. Use this field DOB Month Start rather than your normal Date of Birth and Yellowfin will perform all aggregations correctly.
Before you had something like this:
The report is like that because even though you formatted your Date of Birth to be shown on a report as MMMM yy, Yellowfin still sees underlying data like this:
When the Start of Month function is used, Yellowfin converts all dates into the start of the month format and that is why aggregation becomes possible.
I hope that helps, let me know if you still have any problems.
regards,
Dave
Try the following approach:
Go to your View. Step 2 (View Fields). On the left-hand side panel locate the directory called �Calculated Fields�. Look inside the sub directory called �Date Hierarchy. You should have a Month Start Date function. Drag and drop it to your Available Fields area.
Make sure it is a dimension. Specify a Date Field you would like apply your function against (i.e. it can be Date of Birth). Please refer to the screen shots below:
Then click on the second tab called �Format� and apply your preferred formatting.
Save and activate your new dimension. Use this field DOB Month Start rather than your normal Date of Birth and Yellowfin will perform all aggregations correctly.
Before you had something like this:
The report is like that because even though you formatted your Date of Birth to be shown on a report as MMMM yy, Yellowfin still sees underlying data like this:
When the Start of Month function is used, Yellowfin converts all dates into the start of the month format and that is why aggregation becomes possible.
I hope that helps, let me know if you still have any problems.
regards,
Dave
This is a great solution, but in 7.0 this is no longer an option for calculated fields. Is there now a different way to handle this in 7.0? Thanks,
Travis
Travis
Hi Travis,
Date hierarchies are definately still available in YF 7.
The list of available options is dependent on the database you connecting to.
The most common databases are supported, however there are a few lesser used databases that will not have the option.
Regards,
Stephen
Date hierarchies are definately still available in YF 7.
The list of available options is dependent on the database you connecting to.
The most common databases are supported, however there are a few lesser used databases that will not have the option.
Regards,
Stephen