New pre-defined date filter - financial quarter

Veronika Lackner shared this idea 8 months ago
Idea Logged

I would like to propose a new date filter "financial quarter"


This would be based on a quarter calculated from the start date of the financial year.


This would allow further flexibility for reporting

Comments (15)

photo
1

This should convert the date into a quarter value (1 to 4) allowing easy grouping of quarters and reporting on historical values for comparisson

photo
1

Hi Veronika,

yes, I think that's a great idea, so I have raised an internal enhancement request (YFN-9347) for it.

Thank you for your input.

regards,

David

photo
1

Hi All,


do we know if this progressed at all?


Cheers Peter

photo
1

Hi Peter,

good news, I had a look in a recent build of 7.3 and it looks like it is there now, just make sure your date filter is using the BETWEEN operator rather that EQUAL TO :

/fAGSrVuGLH8AAAAASUVORK5CYIIA

regards,

David

photo
1

Hi David,


that's great news. Thank you for checking. I will make a note re the BETWEEN thing.


Cheers Peter

photo
1

OK, and if there are any issues with it then please let me know.

photo
photo
1

hello again Peter,

actually I've just realised that the second part of your request is not possible (the date should be converted into a quarter value 1-4) via the Financial Quarter Predefined Date Filter.

However, it would be possible if there were a Date Function in the View Builder's Prepare screen called Financial Quarter, however at the moment there is only one called "Quarter" which in fact is Calendar Quarter (I tested it out on the current date and it came out with a 2 instead of 4)

So what I will do is to keep that enhancement request open and add a new part to it: "Please add a new Date Function in the View Builder called "Financial Quarter""

As well as that, I think I should inform you that what you want is currently possible if you create a Calculated Field, choose the "Freehand SQL" Formula Type and then enter in some SQL code that will do the job such as the example below:


CASE
    WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN 1
    WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN 2
    WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN 3
    WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN 4
END
regards,

David

photo
1

Hello David,

Thank you for also informing me about the progress. A new Date Function would be a great idea because our customers often using the financial quarter and if they have to add a calcualtion within every report this is not the best resolution.

Regards,

Veronika

photo
1

Hi Veronika,

yes I agree, a new Date Function called "Financial Quarter" would be great!

One more thing, I mention this just as a workaround until the new Date Function "Financial Quarter" gets created, please keep in mind that the Calculated Field idea I mentioned in my previous email can be created at the view level, thus if you created it just once in a view then all clients would be able to use it in the reports without having to create it again themselves. Also, I gave an example using the Freehand SQL Calculated Field, but please keep in mind that you can achieve the same result using the Formula Builder of the Simple Calculated Field as well.

regards,

David

photo
1

Hi David,

We are going to use your workaround within every single report because we do have customers with different financial quarters. So we cannot create this at the view level. But I get that this should just be a workaround. Thank you.


Regards,

Veronika

photo
1

Hi Veronika,


yes I understand that it is just a workaround, it will certainly be very easy to do with a proper new Date Function called "Financial Quarter" but for the moment this workaround is better than nothing.

So I am glad the workaround is helpful for the moment, thanks for letting me know!


regards,

David

photo
photo
1

Hi David,

thank you for all the info. I'm just having a little trouble getting my head around how to go about what I want to do.

I have a table where sales figures for example are currently broken down by Month. I have created this using cross tab and in the columns I used the Pre-defined Year, pre-defined Month(number so that the months are in the right order, Month Name. I want to use Quarters in the same way so that n my report I have 12 columns of figures, one for each month and then four columns of figures, one fore each quarter.

Would I use your SQL statement above in the Calculated field and that should just work if I drag it into columns are Month Name?


Cheers Peter

photo
1

Hi Peter,

I understand what you are trying to do. You need to add a union and link financial quarter(subquery calculated field) to the month field (master query field) along with the amounts etc.. To make the report even better, use reference codes for your sales figure column rather than sorting with another column. I have an example where I use reference codes with an AR report. The buckets are sorted from 0-30, 31-60, 61-90, etc and in my union I added 'Current Balance'. To show you how effective reference codes are, I changed the sorting to stick the current balance roughly in the middle. You could do the same thing by sticking Q1 after Month 3, Q2 after Month 6, etc. Here is how easy it is:

e4c4d229e7575032f3bda00265600a4a

I used to do the same thing in order to sort the cross tab columns. I even got creative and used headings <h1> through <h12> and chose not to render the HTML. That worked nicely but now I use reference codes. It's better and while it's a little finicky it sorts charts as well. There is a slight glitch when sorting stacked vertical columns but it works perfect for the tabs, other chart types and in your case sorting cross tabs with unions:

58959fbd95b8d282fd955171c2d81735

Best regards,

Larry B

photo
1

Here's what your report should look like. I used Fiscal year to group by but you can use employeed, department in addition to fiscal year very easily by adding it to the row:

73008c43f0be7fcbb1405ba3ea885d74

This will produce

855bd32383a106284ddd69cd05946d16

photo
photo
1

Hi Peter,

yes, unless I've misunderstood you, that is exactly what I meant. To make sure that we aren't misunderstanding one another I have attached a short video to show you what I meant, please let me know if it is the same thing you are talking about - I certainly hope it is!

regards,

David