topic Re: Overlay 2 rolling 12 month charts in one in QlikView

Overlay 2 rolling 12 month charts in one

snehamahaveer — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

My latest requirement is to create a chart that shows two rolling 12 month as a comparison of one another.

I am able to show them separately as 24 month period. However, when I overlay them by month as dimension I am unable to sort them based on the latest month values. The chart automatically sorts from Jan - Dec or Dec-Jan and not by the latest month to the last 12 months.

Can someone please help with this

Thanks in advance.!

Re: Overlay 2 rolling 12 month charts in one

Anil_Babu_Samineni — Mon, 12 Jun 2017 10:18:08 GMT

I assume, You applied 12 moths roll-out expression to Bar one not the Symbol one. Can you apply the same set analysis for 2 metrics then check. If not, I would ask you to share mock up application

Re: Overlay 2 rolling 12 month charts in one

sunny_talwar — Mon, 12 Jun 2017 11:02:48 GMT

How are you sorting your symbol chart? I am guessing that's the one which is not working, right?

Re: Overlay 2 rolling 12 month charts in one

snehamahaveer — Mon, 12 Jun 2017 13:38:35 GMT

Hi Sunny,

I am only able to sort based on month(dimension) and not on expressions. Currently I tried by Month no, Month, or by expression as well. However, I am not able to sort it the based on the latest month.

Kind regards,

Sneha

Re: Overlay 2 rolling 12 month charts in one

sunny_talwar — Mon, 12 Jun 2017 13:43:05 GMT

Would you be able to share a sample for us to take a look at?

Re: Overlay 2 rolling 12 month charts in one

RonaldDoes — Mon, 12 Jun 2017 14:01:13 GMT

Hi Sneha,

If you sort your months according to an expression, and enter

=If(Month(Date)<6, Month(Date)+12, Month(Date))

that would have your month order running from june to may.

Please find an attached example. Does this solve your problem?

With kind regards,

Ronald

Re: Overlay 2 rolling 12 month charts in one

snehamahaveer — Mon, 12 Jun 2017 15:27:13 GMT

Hi Guys,

Thanks for your responses. However, please find attached a test version of the same.

Any help is much appreciated.

Thanks

Apologies - I have changed the expression to the correct one.

Re: Overlay 2 rolling 12 month charts in one

marcus_malinow — Mon, 12 Jun 2017 15:27:42 GMT

Hi Sneha,

use the Sort by Expression option. Expression for this should be max(Date)

Marcus

Re: Overlay 2 rolling 12 month charts in one

snehamahaveer — Mon, 12 Jun 2017 15:28:33 GMT

Thank you Ronald,

This would work when a single 12 rolling month expression is written. Also, I want the sort to be dynamic based on the latest selection made. I have attached a test app - please let me know if you can solve my dilemma

Re: Overlay 2 rolling 12 month charts in one

snehamahaveer — Mon, 12 Jun 2017 15:30:25 GMT

Hi Marcus,

When I use max date, it sorts the latest month first. However - its followed by January? Not sure if its supposed to do that.

Re: Overlay 2 rolling 12 month charts in one

sunny_talwar — Mon, 12 Jun 2017 15:34:44 GMT

What is MonthCount field here? Month_No? You expression uses MonthCount, but I don't see any field with that name?

Re: Overlay 2 rolling 12 month charts in one

marcus_malinow — Mon, 12 Jun 2017 15:37:22 GMT

Hi Sneha,

the file you attached doesn't contain a Date field so this will be difficult to fix in that!

Try max(Month_Conseq_No) instead

Marcus

Re: Overlay 2 rolling 12 month charts in one

sunny_talwar — Mon, 12 Jun 2017 15:38:21 GMT

May be this as your sort expression

Month(Date#(Only({1}Month), 'MMM'))

Re: Overlay 2 rolling 12 month charts in one

snehamahaveer — Mon, 12 Jun 2017 15:50:24 GMT

Hi Sunny,

MonthCount is the a consecutive count of Month over all years. So in 2015 Aug its 1, and Jan 2016 its 6 and so on.

Re: Overlay 2 rolling 12 month charts in one

sunny_talwar — Mon, 12 Jun 2017 15:55:40 GMT

Did my response below makes sense?

Re: Overlay 2 rolling 12 month charts in one

snehamahaveer — Mon, 12 Jun 2017 16:07:52 GMT

Hi Sunny,

Thanks, however this is not dynamic based on the selections made for year/month.

=max(Month_Conseq_No)

The above works fine on Dummy table - however, when I use it in my app it does the same as date. Jun first then Jan..Dec.

Re: Overlay 2 rolling 12 month charts in one

snehamahaveer — Mon, 12 Jun 2017 16:08:38 GMT

Hi Marcus,

max(Month_Conseq_No)

The above works fine on Dummy table - however, when I use it in my app it does the same as date. Jun first then Jan..Dec.

Any thoughts, on what needs to change in my app to accommodate this?

Re: Overlay 2 rolling 12 month charts in one

sunny_talwar — Mon, 12 Jun 2017 16:17:24 GMT

Where is =max(Month_Conseq_No) coming from? I asked you to use Month(Date#(Only({1}Month), 'MMM'))

Re: Overlay 2 rolling 12 month charts in one

snehamahaveer — Mon, 12 Jun 2017 16:23:16 GMT

Hi Sunny,

I did try your expression and as I said its not dynamic. It displays data from Jan - Dec and Dec- Jan. It's not sorting it based on the month selected. That's why I told about the one that works on test file but not on my original data set.

Re: Overlay 2 rolling 12 month charts in one

sunny_talwar — Mon, 12 Jun 2017 16:26:40 GMT

I got it... so for example if you select March, you want the sorting to begin at March, Apr... Dec, Jan, Feb?