Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am attempting to sort a table for Trailing 12 months but I am having issues getting it to show the correct order.
Our fiscal year begins in October and ends in September, so we are at the end of our Fiscal Year. The problem is, it wants to show September last on the chart and it makes it look like this is the count of the current Sept, but really it is Sept 2015, last year.
I would have liked to have it show the Years below the Months to show where the current year and last fiscal year begins and ends.
I had accomplished a similar set up in another table and it looked like this.
But the problem is, this was just comparing one measurement across the current fiscal year and last year, or the last year and the one before that (2 years ago)
So my chart currently looks like this
But there is no way to let the user know just by looking at it that the September in the chart is the September from last year, making it look like those are our current numbers for this September, which are very off since we are only 9 days into the month, and that is showing the ENTIRE month from last year.
So basically its a formatting or sorting issue and I cant figure out a "pretty" way to make it work. Adding the year (15 or 16) to the end of each month just makes the entire chart look cluttered and is difficult to read.
If anyone has had this issue or has an idea of how to solve it, I would appreciate any tips to tricks.
Thank you!
Hi,
As in your first chart, Add the Legend for Year that will give you the Bar color description. Hope this helps.
There is no Year in the Dimension, just the month.
I can't add the year to the dimension because I have two expressions and it makes it look like this. I don't want the bars stacked, I need them side by side for comparison.
Hi,
Can you share your qvw that will help to debug/explore more options.
Hi Crystle,
Do you use a master calender? If so you could add FinYear to your calendar script.
Something like:
FinYear = Text(Year(Date) + Floor(num(month(Date))/10)-1) & ' / ' & Text(Year(Date) + Floor(num(month(Date))/10))
Good luck
Andrew
Creating the legends in text box/or a block chart with the year as dimension & '1' as expression and adjusting its width that it just looks like legend and place them over the chart might be one of the sol'n..
Yes I do have a master calendar and I tried adding the expression you described but I'm not sure how I could apply it as a solution to my problem?
I could, but the data isn't really important I think.
The dimension is Fiscal Months
The two Expressions are Sums of two fields. And one expression that is the division of the other two expressions.
The problem is my sorting of the bar charts without them being stacked.
Hi Crystle,
If you add this field to your calendar
FinYear = Text(Year(Date) + Floor(num(month(Date))/10)-1) & ' / ' & Text(Year(Date) + Floor(num(month(Date))/10))
then if you select the current financial year '2015 / 2016' in the UI or in the expression set
{$<FinYear = {'2015 / 2016'}>}
you will exclude Sept 2015.
An improvement might be to define FinYear as a Dual:
FinYear = Dual(Text(Year(Date) + Floor(num(month(Date))/10)-1) & ' / ' & Text(Year(Date) + Floor(num(month(Date))/10)),
Year(Date) + Floor(num(month(Date))/10)-1)
so we have a text string to display '2015 / 2016' and a numeric value 2015. Then you can use
{$<FinYear = {$(=max(FinYear))}>}
Cheers
Andrew
Hi Crystal,
You can try using FY 2015-16,
FY 2016-17,
FY 2017-18 and so on in the Year Dimension.
Something like this:
dual('FY '&Date(FiscalYear,'YY') &'-'& Date(AddYears(FiscalYear,1),'YY'),FiscalYear) as DisplayFiscalYear
Thanks,
Chetan