How can I sort and aggregate by MMMM yy
14 May, 2013
Hi,
I would very much be liking to sort my Date Of Birth column that is Month-Year (example: November-12).
But if I am using the Org Ref Code method, then I am thinking that I would be having to create a code for every month for the previous 100 years.
Is there being an easier method for me (please do not be thinking that I am a lazy fellow. No my friend, it is just that I am of the opinion that there is no point in re-inventing the wheel - I hope you can understand)
thanking you in advance
Venkataratnam
I would very much be liking to sort my Date Of Birth column that is Month-Year (example: November-12).
But if I am using the Org Ref Code method, then I am thinking that I would be having to create a code for every month for the previous 100 years.
Is there being an easier method for me (please do not be thinking that I am a lazy fellow. No my friend, it is just that I am of the opinion that there is no point in re-inventing the wheel - I hope you can understand)
thanking you in advance
Venkataratnam
Hi Venkataratnam,
Go to your View, then advance to the Step 2 screen (View Fields). On the left side panel locate directory called �Calculated Fields�. Look inside sub directory called �Date Hierarchy. You should have 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 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.
Incidently, if you previously tried just formatting the Date of Birth field as MMMM yy then you would have got a result like this:
That happened because even though you had formatted your Date of Birth to be shown on a report as MMMMM yy, Yellowfin still sees underlying data like this:
Whereas when the Start of Month function is in use, Yellowfin converts all dates into start of the months and that is why aggregation becomes possible:
I hope that helps your report, please let us know if there are any problems setting this up.
Regards,
Dave
Go to your View, then advance to the Step 2 screen (View Fields). On the left side panel locate directory called �Calculated Fields�. Look inside sub directory called �Date Hierarchy. You should have 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 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.
Incidently, if you previously tried just formatting the Date of Birth field as MMMM yy then you would have got a result like this:
That happened because even though you had formatted your Date of Birth to be shown on a report as MMMMM yy, Yellowfin still sees underlying data like this:
Whereas when the Start of Month function is in use, Yellowfin converts all dates into start of the months and that is why aggregation becomes possible:
I hope that helps your report, please let us know if there are any problems setting this up.
Regards,
Dave