topic Re: Previous Quarter Year Calculation in QlikView

Previous Quarter Year Calculation

BootCam — Sat, 16 Nov 2024 04:54:59 GMT

I have Quarter Year field in this format, 20183, 20182, 20181, 20174, 20173, 20172, 20171 and so on.

I would like to create a variable for the the previous quarter calculation.

So, When the user select 20183 they will see the current Quarter value and 20182 will be the previous Quarter value.

I was trying with this: =Left(QuarterYear,4)&(Right(QuarterYear,1)-1) but when i select 20181 its giving me 20180 rather than 20174.

Any help?

Re: Previous Quarter Year Calculation

albert_guito — Thu, 20 Dec 2018 16:51:07 GMT

Hi,

For a selected date you can calculate the previous quarter as:

 'Q' & If(Ceil(Month(Date)/3)-1=0,4,Ceil(Month(Date)/3)-1)

Ag+

Re: Previous Quarter Year Calculation

BootCam — Thu, 20 Dec 2018 16:55:47 GMT

Hi Ag+,

Thanks for your reply but its not giving the desired results.

When i select 20171, 20172, 20173 its showing quarter Q4.

Thanks,

Re: Previous Quarter Year Calculation

albert_guito — Thu, 20 Dec 2018 17:05:14 GMT

Sorry, i was thinking on standard quarter format ...

Use

If(Ceil(Month(Date)/3)-1=0
   ,Year(Date)-1 & 4
   ,Year(Date) & Ceil(Month(Date)/3)-1
)

Ag+

Re: Previous Quarter Year Calculation

BootCam — Thu, 20 Dec 2018 17:20:15 GMT

Please see attached file. i am still not getting the desired results. In the text box i add your calculation.

Thanks,

Re: Previous Quarter Year Calculation

sunny_talwar — Thu, 20 Dec 2018 17:25:37 GMT

I suggest using QuarterStart() function to create a quarter field which can then be used in set analysis.

LOAD QuarterStart(Date) as QuarterYear

and in your set analysis you can do this

Sum({<QuarterStart = {"$(=QuarterStart(Max(QuarterYear), -1))"}>} Measure)

Re: Previous Quarter Year Calculation

BootCam — Thu, 20 Dec 2018 17:31:51 GMT

Hi Sunny I do not have a Date field, I have a Quarter Year field only. The example values are as i mentioned in my previous post like in this format, 20183, 20182, 20181, 20174, 20173, 20172, 20171 and so on.

Thanks,

Re: Previous Quarter Year Calculation

sunny_talwar — Thu, 20 Dec 2018 17:35:51 GMT

Create a QuarterYear field like this

=QuarterStart(MakeDate(Left(QuarterYear, 4), Right(QuarterYear, 1)*3)) as QuarterYear_New

and then use the set analysis on the QuarterYear_New field.

Re: Previous Quarter Year Calculation

BootCam — Thu, 20 Dec 2018 18:12:40 GMT

Hi Sunny,

What is this QuarterStart field in your set analysis that you are comparing to?

Sum({<QuarterStart = {"$(=QuarterStart(Max(QuarterYear), -1))"}>} Measure)

Thanks,

Re: Previous Quarter Year Calculation

sunny_talwar — Thu, 20 Dec 2018 18:21:15 GMT

My bad... should be QuarterYear

Sum({<QuarterYear = {"$(=QuarterStart(Max(QuarterYear), -1))"}>} Measure)

Re: Previous Quarter Year Calculation

BootCam — Thu, 20 Dec 2018 18:27:19 GMT

Hi Sunny,

As you mentioned,

So if i create the new field as

=QuarterStart(MakeDate(Left(QuarterYear, 4), Right(QuarterYear, 1)*3)) as QuarterYear_New

Then the set analysis would be:

Sum({<QuarterYear_new = {"$(=QuarterStart(Max(QuarterYear_nee), -1))"}>} Measure)

Is that right?

Thanks,

Re: Previous Quarter Year Calculation

sunny_talwar — Thu, 20 Dec 2018 18:29:46 GMT

Yup, this

Sum({<QuarterYear_new = {"$(=QuarterStart(Max(QuarterYear_new), -1))"}>} Measure)

Re: Previous Quarter Year Calculation

BootCam — Thu, 20 Dec 2018 19:29:19 GMT

Thanks Sunny!

I am Not sure why but when i do sum(Measure) i get some value for example but with your calculation i get 0.

Thanks,

Re: Previous Quarter Year Calculation

sunny_talwar — Thu, 20 Dec 2018 19:30:19 GMT

Are you making selection in any date or time field?

Re: Previous Quarter Year Calculation

BootCam — Thu, 20 Dec 2018 19:33:27 GMT

Yes, I am making selections on the QuarterYear field.

Re: Previous Quarter Year Calculation

sunny_talwar — Thu, 20 Dec 2018 19:36:13 GMT

Then you need to ignore selection in that field because selection and set analysis are contradicting each other

Sum({<QuarterYear_new = {"$(=QuarterStart(Max(QuarterYear_new), -1))"}, QuarterYear>} Measure)

Re: Previous Quarter Year Calculation

BootCam — Thu, 20 Dec 2018 19:51:20 GMT

Hi Sunny,

If i cant get values by That field selections then the solution wont be useful for my situation.

Thanks,

Re: Previous Quarter Year Calculation

sunny_talwar — Thu, 20 Dec 2018 20:15:41 GMT

My friend, have you even tried it? 🙂 do everything as you would want to... but just use the new expression

Re: Previous Quarter Year Calculation

BootCam — Thu, 20 Dec 2018 20:23:26 GMT

Hi Sunny, I actually tried before replying to you.

But ignoring the selection field has no effect on the calculation. So how do i get any previous period based on the selection if i cant select the field?

I am confused though.

Thanks,

Re: Previous Quarter Year Calculation

sunny_talwar — Thu, 20 Dec 2018 20:28:49 GMT

So, you select a single QuarterYear and use this expression

Sum({<QuarterYear_new = {"$(=QuarterStart(Max(QuarterYear_new), -1))"}, QuarterYear>} Measure)

and nothing happens? What do you mean when you say nothing happens? See 0s? or nothing get filtered out?

Would you be able to share a sample to show the issue? This can be a small issue, but very difficult to know without something to look at.