Get Previous Quarter in QLIK Sense

[harika189]

I want to get the previous quarter dynamically based on the selection in Month field. In this case the data is available for Oct-21 (Q4-21) and I want to get Jun-21(Q2-21) to show in label of KPI.

Currently this is the formula I am using : 'Q'&ceil(Month(QuarterStart(max(FDAY),-1))/3) which is not working for this case.

Can you guide me on the correct formula I have to use?

[KGalloway]

if(GetSelectedCount([Month-Year]) = 1, 'Q' & month(Quarterend(addmonths(Date , -3))) / 3)

The above code in a KPI chart works for me with the following restrictions:

1. I use an if statement to make this measure only appear when a single Month-Year value is selected.

2. The [Date] field that is used in addmonths() only works for mine because there was only one date for each month-year value in the sample data I used. You can use avg(Date) if there is more than one Date per Month-Year in your data. This worked for me too.

[harika189]

Hello KGalloway ,

Let me explain me in detail I have FDAY in format( MM/DD/YYYY 12:00:00 AM).The KPI Label should take the max of FDAY as per filter ,so as per above example the max value is OCT-21 and we should get previous available quarter as per data available to it dynamically in this case the data available before Oct-21 for this selection is Jun-21 and should come as Q2-21.

I have tweaked the formula and tried it ,but instead of giving Q2 its showing Q3 which is not correct.

If don't need any Getselectedcount  as we need to take max date available and give it previous available quarter value

Can you look into this an provide an answer.

[vinieme12]

Try below

=MonthName(QuarterEnd(Max(FDAY),-2))&'(Q'&ceil(Month(QuarterStart(Max(FDAY),-2))/3)&'-'&Year(QuarterEnd(Max(FDAY),-2))&')'

[harika189]

Hello @vinieme12 ,

For this case it’s failing I have jan 23 data which is Q1 23 and in this case my previous quarter is Q4-22

Based on above formula it’s giving Q3-2022 which is wrong