Unable to create a Year on Year report running on Financial Year Date

Peter Doornbos shared this question 12 months ago
Answered

Hi,


we have been trying various ways to create a report that is run on the Australian Financial Year, JUL - JUN.


We are writing it in Cross Tab and have Cost Centres down the first left hand column. This is then followed by Month under which we have the costs for this year, then last year and the then the Variance between the two. Something like:


July

This Year. Last Year. Variance.


If we include the year in the columns Tab, then we get the month repeating for each year.


2018 2017

July AUG JUL AUG

This Year. Last Year. Variance. This Year. Last Year. Variance This Year. Last Year. Variance. This Year. Last Year. Variance


So not only do we get the comparisons under the month, the months are also repeated under each year.


If we remove the Year from the columns tab and also the join between the different views, then it shows correctly as far as no year at the top and no duplicated months. We are however, only able to show a January to December year where if the month are in the future, such as May and June now for 2018, it will show the figures for 2017/2016 butcompletely out of order as per the attached.


Is there a way around this?


Best regards Peter

Comments (15)

photo
1

Hi Peter,

did you consider using an Append Subquery? If you go to the following wiki article:

http://wiki.yellowfin.com.au/display/USER74/Append+Sub+Query

you will see an example there of a year-on-year report built by using the Append Subquery, it is not a cross tab report like yours, and also it doesn't have the Month column, but I think it may provide a solution if you play around with it.

Please let me know how it goes.

regards,

David

photo
1

Hi David,

yes, I should have explained better.

I have two subqueries, the second SQ is for this year, the first is for last year and the master is to calculate any variances between the two.

I am using Cross Tab.


Regards Peter

photo
1

Hi Peter,

OK, I think I'm going to have to set this up over here to investigate it. Which column are you joining the subqueries on?

regards,

David

photo
1

also, about the Year and Month columns, are they based on the Issue Date column? If so, which Date Function did you use?

thanks,

David

photo
1

Hi Dave,

thank you for looking at this. I have attached a Word Doc with screen shots of the three queries as well as the filters used in each.

Please let me know if I can provide more detail.

Best regards Peter

photo
1

Hi Peter,

thanks for all the documentation, after studying it I think I have created a vaguely similar report over here, and then after some experimentation with it the best solution I could find was as shown below:

/f6CI78BGQRzSAAAAAElFTkSuQmCCAA==


which was done by creating a special set of Reference Codes called Month(FinYr) and giving them the custom sort order that was required:

/x8mm87tx0GWdwAAAABJRU5ErkJgggA=


and also I had to add a new calculated field to the view that showed the month number, and then this was formatted as Reference Code and of course the new Month(FinYr) codes were selected.

I hope this solution fulfills your reporting requirements. It was the best I could find, keep in mind I am not a consultant, and consequently I imagine a consultant might be able to come up with other solutions, so if you would like further ideas then I can arrange with your account manager to organise one of our consultants for you.

Please let me know what you think.

regards,

David

photo
1

Hi David,

thank you for this. My only issue is that I don;t have access to these reference tables. It looks great other4wise.


Regards Peter

photo
1

Hi Peter,

You don't need access to any tables, you actually create the Ref Codes in the View Builder, here are the steps (for 7.2, it's different and easier in 7.4):

/oWAAAAAElFTkSuQmCCAA==


regards,

David

photo
1

Hi David,

that would be nice, but here at tramada we have pre-set Views which we can choose from but making the any change can only be done centrally and can be along drawn out process, so not really an option.

Regards Peter

photo
1

HI David,


sorry an after thought. Whilst the numbering of the months as you have suggested is good, it will make it a very rigid report. she may want to run other date ranges in which case we will have the same problem come back, but in this case the default will be JUL to JUN instead of JAN to DEC. Any other ideas?


Regards Peter

photo
1

Hi Peter,

the other date ranges wouldn't be a problem, you would just create a different set of reference codes, for example, Months(UK FinYr) and make sure that April has the Sort Order of 1, and seeing as they only take a few minutes to create it would be no big issue to create many different sets.

I should also mention that from Yellowfin 7.3 onwards, you can create new Reference Codes in the Administration section of the app, i.e. you don't need to modify the view anymore to do it:

/Bzs7KphFG4ZIAAAAAElFTkSuQmCCAA==


Regarding any other ideas, sorry but I don't have any, however I will ask my colleagues for their input, I'm sure they'll have something.

regards,

David

photo
1

Hey Dave, I actually like your idea of using Org Ref codes to set the order, as org refs codes are there to enforce sorting (along with providing descriptions).

I can also confirm that you are able to create org ref codes at the report level (so no need to access the view), from 7.3 on wards:

a368185fc3b47bd0e9ce11a4eaa36e37


Another option would also be to remove cross-tabs from the picture. Cross-tabs perform additional calculations and it needs to be displayed in a certain way for it to makes sense.


Regards,

David

photo
1

Hi Gents,


thank you for all your thoughts on this, I appreciate it.


So to summarise. The only way I can achieve this is to use OrgRef to assign different ranges of months. Eg, for a standard year I would need to assign: 1= Jan 2=Feb 3=Mar etc.

For an AU financial year I would need to assign another like: 1-Jul 2=Aug 3=Sep etc.

So no ability to simply run a report on the fly and pick a month range at random?


The ability to create or maintain OrgRef at the report level will not be available until 7.3, right?


Thank you again.


Cheers Peter

photo
1

Hi Peter,

I can confirm that the 3 points you raise in your summary are all correct.

regards,

David

photo
1

Great, thanks for your help.


Cheers Peter

photo