Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
BootCam
Creator
Creator

Previous Quarter Year Calculation

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?

Labels (2)
22 Replies
albert_guito
Creator II
Creator II

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+

 

Ag+
BootCam
Creator
Creator
Author

Hi Ag+,

 

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

 

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

 

Thanks,

BC

albert_guito
Creator II
Creator II

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+

 

Ag+
BootCam
Creator
Creator
Author

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

 

Thanks,

BC

sunny_talwar

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)
BootCam
Creator
Creator
Author

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,

BC

sunny_talwar

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.

BootCam
Creator
Creator
Author

Hi Sunny,

 

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

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

 

Thanks,

BC

sunny_talwar

My bad... should be QuarterYear

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