topic Re: Rolling Quarters Data in QlikView

Rolling Quarters Data

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

Hi All,

I am attaching both sample excel file and sample qlikview document.

I want to make a rolling bar chart for quarterly data. The last bar will represent last possible quarter as per the data and previous bars will represent last n quarters depending upon the requirements of n.

My year start with April every year. So Q1 means Apr, May and Jun.

Please guide me in making the expression.

For Rolling Months data I am using following expression at present :

[Mon-Y]={">$(=Num(Addmonths(Max([Mon-Y]),-5))) <=$(=Max([Mon-Y]))"} - for 5 rolling periods

and dimension I am using is Date([Mon-Y],'MMM-YY')

Now I want to make similar expression for rolling quarters for dimension representing quarters.

Regards,

Manish Prasad

Re: Rolling Quarters Data

balar025 — Fri, 07 Jul 2017 04:50:02 GMT

Hi Manish,

Can you please share your expected output ?

-Ravi Balar

Re: Rolling Quarters Data

manpri7078 — Fri, 07 Jul 2017 04:57:51 GMT

Dear Ravi,

I have attached the desirable bar chart already. But it is showing all the quarters data as per sample excel file. I want to define a variable for example 2. And the bar chart will show last two quarters data. if I define it 3 it shows for 3 quarters and so on.

I hope I have clarified your query.

Regards

Manish Prasad

Re: Rolling Quarters Data

balar025 — Fri, 07 Jul 2017 05:14:55 GMT

Hi Manish,

As i understand, you requirement to show, conditional Quarter

for example if i select Apr-17 which falling Q1-17.

So, Q1-17, Q4-16,Q3-16 if my variable is define as 2.

Q1-17, Q4-16,Q3-16, Q2-16 if my variable is define as 3. So on...

- Ravi Balar

Re: Rolling Quarters Data

antoniotiman — Fri, 07 Jul 2017 05:15:15 GMT

Hi Manish,

see Attachment,

Regards,

Antonio

Re: Rolling Quarters Data

manpri7078 — Fri, 07 Jul 2017 06:15:22 GMT

Hi Antonio,

Thanks for your guidance. It now fulfills my purpose. It should be marked as correct.

However I am now curious to know more. Can this type of expression be made for Half Years as well. For example HY1 means Q1 + Q2 and HY2 means Q3 + Q4. And also like wise Year ones.

If so can you help me again.

Regards,

Manish Prasad

Re: Rolling Quarters Data

antoniotiman — Fri, 07 Jul 2017 06:36:24 GMT

See Attachment.

Re: Rolling Quarters Data

manpri7078 — Mon, 10 Jul 2017 06:19:01 GMT

Hi Antonio,

I mean to know about rolling HYs just as for rolling Quarters for which your guidance worked perfectly for me.

For example If I select 2, then the bar chart should show last 2 HYs data and so on just as you have done for Quarters data.

I am attaching sample excel file again with some modifications to incorporate HY data.

Please guide me accordingly.

Re: Rolling Quarters Data

antoniotiman — Mon, 10 Jul 2017 10:56:46 GMT

See Attachment.

Re: Rolling Quarters Data

manpri7078 — Mon, 10 Jul 2017 11:41:33 GMT

Hi Antonio,

As per my sample data, my outcome should be the following If I select 3 from the slider object :

1. Since Q1 data for year 2018 is available only up to May 2017 hence my last bar to show same value as 251279 (Q1-18).

2. Now my second last bar to show value for H2-17 which is 755279

3.And my third last bar to show value for H1-17 which is 719949.

This is my expectation when I am selecting 3 from the slider object.

Hope I am more clear now of my expectations

Regards

Manish Prasad

Re: Rolling Quarters Data

antoniotiman — Mon, 10 Jul 2017 12:14:08 GMT

See new Attachment.

Re: Rolling Quarters Data

manpri7078 — Tue, 11 Jul 2017 03:35:05 GMT

Hi Antonio,

My Financial year starts with Apr every year so Last bar will show H1-18 and it will consist of only Apr-17 and May-17 data of 2018 as of now as per this sample file.

Now the previous bar should show data from Oct-16 to Mar-17 and bar before this should show data from Apr-16 to Sep-16. This is what I want If I select 3 from the slider object.

Regards

Manish Prasad

Re: Rolling Quarters Data

antoniotiman — Tue, 11 Jul 2017 07:01:54 GMT

See attachment

Re: Rolling Quarters Data

manpri7078 — Tue, 11 Jul 2017 12:07:32 GMT

Hi Antonio,

when selecting 2 from the slider object it is showing wrong.

Regards

Manish Prasad

Re: Rolling Quarters Data

antoniotiman — Tue, 11 Jul 2017 13:34:02 GMT

New Attachment.