topic Re: Previous Quarter in QlikView

Previous Quarter

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

I need to create 2 variables to get the Max Quarter and the previous quarter

MY EXPRESSION :

(Max Quarter - Previous Quarter)

/Previous Quarter

i am able to get max quarter by using =MaxString(YrQtr) but cannot get previous quarter

Please find the attached app

Thanks

Re: Previous Quarter

Digvijay_Singh — Thu, 05 Apr 2018 21:43:51 GMT

Something like this -

QuarterStart(Max(Date),-1)

Re: Previous Quarter

stevejones1 — Thu, 05 Apr 2018 22:18:37 GMT

My dates are based off of Fiscal calendar , and this is not giving me the right quarter , i expect FY18-Q4 for previous quarter

Re: Previous Quarter

Digvijay_Singh — Thu, 05 Apr 2018 22:22:45 GMT

I think you can adjust according to Fiscal calendar with the help of 3rd parameter -

QuarterStart(date[, period_no[, first_month_of_year]])

first_month_of_year

If you want to work with (fiscal) years not starting in January, indicate a value between 2 and 12 in first_month_of_year.

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/quarterstart.htm

Re: Previous Quarter

stevejones1 — Thu, 05 Apr 2018 22:30:14 GMT

I want to see FY18-Q4 as the previous quarter value.is that possible?

Re: Previous Quarter

Digvijay_Singh — Thu, 05 Apr 2018 22:45:14 GMT

You mean the value as 'FY18-Q4'? or the date type date for the first day of Quarter 4 2018. Can you share some sample data and expected output?

Re: Previous Quarter

stevejones1 — Thu, 05 Apr 2018 22:54:46 GMT

Attaching my application. !

Re: Previous Quarter

Digvijay_Singh — Thu, 05 Apr 2018 22:58:28 GMT

I cannot open qvw, its unfortunate Qlik doesn't allow to open qvw with personal edition. Hopefully someone having license would help.

Re: Previous Quarter

Digvijay_Singh — Thu, 05 Apr 2018 23:15:05 GMT

may be this can work if Maxstring worked for you to get 2nd highest-

MaxString(YrQtr,2)

Re: Previous Quarter

stevejones1 — Thu, 05 Apr 2018 23:23:52 GMT

unfortunately no, i get an error msg saying maxstring takes only 1 parameter

Re: Previous Quarter

Nagaraju_KCS — Fri, 06 Apr 2018 04:46:40 GMT

Try like below..

vMaxQuarter = Ceil(num(month(max(OrderDate)))/3)

vPriorQuarter = Ceil(num(month(addmonths(max(OrderDate),-3)))/3)

Re: Previous Quarter

passionate — Fri, 06 Apr 2018 15:25:01 GMT

Hi Steve,

Previous Quarter as

=pick(match(MaxString(Quarter),'Q1','Q2','Q3','Q4'),'Q4','Q1','Q2','Q3')

Regards,

Pankaj

Re: Previous Quarter

stevejones1 — Fri, 06 Apr 2018 21:36:58 GMT

Please take a look at attached app

My quarter formats are like FY17-Q4, none of the above expressions work for previous quarter value.

Please help!

Re: Previous Quarter

sasiparupudi1 — Sun, 08 Apr 2018 16:21:52 GMT

Hi Steve,

I suggest you make your fiscal quarter field a dual or create a new field fiscalquarterPeriod using the autonumber so that you can easily use "max" function to get to the max periods

ex:

Dual(YrQtr,AutoNumber(YrQtr,'YrQtr')) as YrQtr

AutoNumber(YrQtr,'YrQtr')) as YrQtrPeriod

hth

Sasi

Re: Previous Quarter

stevejones1 — Mon, 09 Apr 2018 02:17:22 GMT

easy solution 🙂

Re: Previous Quarter

tresB — Mon, 09 Apr 2018 06:31:25 GMT

One front-end solution could be:

=FirstSortedValue( YrQtr,-Aggr(Max(Mid(YrQtr,3,2)*10+Right(YrQtr,1)),YrQtr),2)

Re: Previous Quarter

sasiparupudi1 — Tue, 10 Apr 2018 09:06:49 GMT

Please close the thread by marking a correct answer