Unable to create a Year on Year report running on Financial Year Date
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
The same question 
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
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
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
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
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
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
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
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
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
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
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:
which was done by creating a special set of Reference Codes called Month(FinYr) and giving them the custom sort order that was required:
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
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:
which was done by creating a special set of Reference Codes called Month(FinYr) and giving them the custom sort order that was required:
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
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
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
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):
regards,
David
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):
regards,
David
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
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
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
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
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:
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
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:
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
Replies have been locked on this page!