New pre-defined date filter - financial quarter

Veronika Lackner shared this idea 10 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 (23)

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

photo
1

Hi Dave,


I just wanted to ask if there are any News?


Regards,

Veronika

Ver

photo
1

Hi Veronika,

I've checked the enhancement request and there has been no movement on it yet.

regards,

David

photo
1

Hi Dave,

hmm okay thank you for the information.

Regards,

Veronika

photo
1

Hi Veronika,

if it's very important to you then please let me know and I will try my best to fight for it.

regards,

David

photo
1

Hi David,

it would be great to have this feature because our clients start to create reports and some of them do have different accounting years.

And in our business it is very important to calculate the costs by quarter. So I would be thankful if you could try to fight for it 😊

Regards,

Veronika

photo
1

Hi Veronika,

I'm sorry but after I put forward my convincing argument that it would be a quick enhancement to do, and it would help many clients, I was told "I don't see this being looked at anytime soon." I guess that is because all developers are very busy at the moment working towards the release of the next major version.

The only other thing I can suggest is to contact your Account Manager and discuss it with them, because sometimes they can work some magic!

regards,

David

photo
1

Hi David,

okay thank you for your information and effort in my issue.

I guess I will look through all my ideas with are created for me and then I will get in touch with my account manager.

Maybe it is better if I do have more issues to be solved 😊

Regards,

Veronika

photo
1

Hi Veronika,

yes, good idea, it makes more sense to discuss all outstanding defects in the same meeting rather than one at a time.

regards,

David