topic Re: Sorting Trailing 12 Months in QlikView

Sorting Trailing 12 Months

crystles — Fri, 09 Sep 2016 14:16:01 GMT

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!

Re: Sorting Trailing 12 Months

krishnacbe — Fri, 09 Sep 2016 14:20:48 GMT

Hi,

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

Re: Sorting Trailing 12 Months

crystles — Fri, 09 Sep 2016 14:25:55 GMT

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.

Re: Sorting Trailing 12 Months

krishnacbe — Fri, 09 Sep 2016 14:43:26 GMT

Hi,

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

Re: Sorting Trailing 12 Months

effinty2112 — Fri, 09 Sep 2016 15:15:37 GMT

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

Re: Sorting Trailing 12 Months

neelamsaroha157 — Fri, 09 Sep 2016 15:16:58 GMT

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

Re: Sorting Trailing 12 Months

crystles — Mon, 12 Sep 2016 17:19:09 GMT

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?

Re: Sorting Trailing 12 Months

crystles — Mon, 12 Sep 2016 17:21:15 GMT

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.

Re: Sorting Trailing 12 Months

effinty2112 — Tue, 13 Sep 2016 09:01:24 GMT

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

Re: Sorting Trailing 12 Months

chetansehgal — Tue, 13 Sep 2016 10:42:24 GMT

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