topic Re: Quarter Refernce Lines in QlikView

Quarter Refernce Lines

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

I'm working on a Grid chart with the X dimension being a "Implementation Date". The Implementation Date can be any date in 2017. Is there a way to write an expression that draws reference lines to at the start or end of each quarter?

Re: Quarter Refernce Lines

vishsaggi — Wed, 26 Apr 2017 20:58:44 GMT

Can you share a sample output you are looking for? Any sample data to look into please let us know?

Re: Quarter Refernce Lines

brf10043 — Wed, 26 Apr 2017 21:06:17 GMT

Sure, Here's my current chart. I'd like to add vertical lines to indicate the transition from one quarter to another. Once of the challenges is that I can't predict what the Implementation dates are going to be so I need the expression to look at the Implementation dates and determine where the lines should be.

Re: Quarter Refernce Lines

vishsaggi — Wed, 26 Apr 2017 21:22:02 GMT

Do you have a calendar script used in this? Can you share some sample data here?

Create a field like this may be, i have not tried.

'Q' & Ceil(Month(Max(Date(Date#(S_ImprovementImpDate, 'DD-MMM-YYYY'), 'DD/MM/YYYY')))/3) AS MaxQuarter

Re: Quarter Refernce Lines

vishsaggi — Wed, 26 Apr 2017 21:24:59 GMT

Also check from Help:

QuarterEnd(date [, shift = 0 [, first_month_of_year = 1]])

Returns a value corresponding to a timestamp with the last millisecond of the quarter containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the quarter which contains date. Negative values in shift indicate preceding quarters and positive values indicate succeeding quarters. If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

Examples:

quarterend ( '2005-10-29' ) returns '2005-12-31' with an underlying numeric value corresponding to '2005-12-31 23:59:59.999'

quarterend( '2005-10-29', -1 ) returns '2005-09-30' with an underlying numeric value corresponding to '2005-09-30 23:59:59.999'

quarterend ( '2005-10-29', 0, 3 ) returns '2005-11-30' with an underlying numeric value corresponding to '2005-11-30 23:59:59.999'

Re: Quarter Refernce Lines

brf10043 — Wed, 26 Apr 2017 21:28:30 GMT

I am creating a Quarter value in my load to build a calendar but I can't seem to build reference lines on my Grid chart using it. FYI - S_Implementation date is linked to Comm_Date

'Q' & ceil(month(Comm_Date) / 3) AS Quarter,

Re: Quarter Refernce Lines

vishsaggi — Wed, 26 Apr 2017 21:33:47 GMT

Is it possible, can you share your app to look into?

Re: Quarter Refernce Lines

brf10043 — Wed, 26 Apr 2017 21:39:10 GMT

Here is a stripped down version. The load script is in tact.

Re: Quarter Refernce Lines

vishsaggi — Wed, 26 Apr 2017 21:44:02 GMT

I am getting access denied for this QV document.

Re: Quarter Refernce Lines

brf10043 — Wed, 26 Apr 2017 21:49:02 GMT

Apologies. Try this version

Re: Quarter Refernce Lines

vishsaggi — Wed, 26 Apr 2017 22:23:00 GMT

Hmm Strange it is not working as expected. Can you try like giving the S_Improv...Date in Reference line hardcoded for Q1.

Some reference here: Check it:

line riferences on grid chart | Qlik Community

Re: Quarter Refernce Lines

brf10043 — Wed, 26 Apr 2017 22:35:30 GMT

Not quite sure I understand what you mean

Re: Quarter Refernce Lines

brf10043 — Wed, 26 Apr 2017 22:50:02 GMT

I feel like I'm getting close. I'm currently working on this expression. I would have expected it to draw a line at the end of March, but it drew it and the first date on the chart

If(inquarter([S_Improvement Implemented Date], '30-Mar-2017', 0)='1',-1,0)

Re: Quarter Refernce Lines

sunny_talwar — Thu, 27 Apr 2017 01:30:37 GMT

Something like this?

Re: Quarter Refernce Lines

vishsaggi — Thu, 27 Apr 2017 01:45:48 GMT

This is amazing. Thanks sunny for looking into this. Learnt something new. 🙂

Re: Quarter Refernce Lines

brf10043 — Thu, 27 Apr 2017 14:19:16 GMT

Sunny, that is perfect. Thanks for your Help! I can't seem to find the option to mark this as correct in the discussion. I did mark it as Helpful though!

It is exactly what I need!