Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
crystles
Partner - Creator III
Partner - Creator III

Sorting Trailing 12 Months

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!

9 Replies
krishnacbe
Partner - Specialist III
Partner - Specialist III

Hi,

As in your first chart, Add the Legend for Year that will give you the Bar color description. Hope this helps.

crystles
Partner - Creator III
Partner - Creator III
Author

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.

krishnacbe
Partner - Specialist III
Partner - Specialist III

Hi,

Can you share your qvw that will help to debug/explore more options.

effinty2112
Master
Master

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

neelamsaroha157
Specialist II
Specialist II

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..

crystles
Partner - Creator III
Partner - Creator III
Author

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?

crystles
Partner - Creator III
Partner - Creator III
Author

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.

effinty2112
Master
Master

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

chetansehgal
Creator
Creator

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