Solved: Re: Overlay 2 rolling 12 month charts in one - Qlik Community

snehamahaveer · ‎2017-06-12

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

sunny_talwar · ‎2017-06-12

Try this

=Num(Month(Date#(Only({1} Month), 'MMM'))) - Num(Month(Date#(Only(TOTAL Month), 'MMM'))) + 1 +

If(Num(Month(Date#(Only({1} Month), 'MMM'))) - Num(Month(Date#(Only(TOTAL Month), 'MMM'))) + 1 <= 0, 12, 0)

View solution in original post

Anil_Babu_Samineni · ‎2017-06-12

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

sunny_talwar · ‎2017-06-12

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

snehamahaveer · ‎2017-06-12

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

sunny_talwar · ‎2017-06-12

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

RonaldDoes · ‎2017-06-12

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

snehamahaveer · ‎2017-06-12

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.

marcus_malinow · ‎2017-06-12

Hi Sneha,

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

Marcus

snehamahaveer · ‎2017-06-12

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

snehamahaveer · ‎2017-06-12

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.