Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

pooja821
New Contributor III

finding previous quarter from recent

I want that whenever we select Quarter Q1 even if the multiple quarters are selected, then it should get replaced by Q4.

- In order to get Quarter value Q1 I m using if() condition i.e. if(mid(Quarters,2)=1

- For multiple Quarter Selections I m using CONCAT() function.

- In order to replace Q1 with Q4 I m using variable Max_Quarter. which makes my expression as

concat( distinct if(mid(Quarters,2)=1,$(Max_Quarter)),',')


Current Quarter                Previous Quarter

Q4                                      Q3

Q3                                      Q2

Q2                                      Q1

Q1                                      Q0

My Requirement is that this Q0 should be replaced by Q4 of the previous year i.e.

Current Quarter                  Previous Quarter

Q4                                      Q3

Q3                                      Q2

Q2                                      Q1

Q1                                      Q4

NOTE: Previous year logic has already been implemented in Max_Quarter variable.

Please suggest changes in already build expression i.e. in

concat( distinct if(mid(Quarters,2)=1,$(Max_Quarter)),',')

3 Replies
vinieme12
Esteemed Contributor II

Re: finding previous quarter from recent

when you create you Quarters also create a numeric quarter number

example

QuaterName  = QuarterNumber   

Q4-2016 =  20164

Q1-2017 =  20171

Q2-2017 =  20172

Q3-2017 =  20173

Q4-2017 =  20174


then if you use Autonumber(QuarterNumber ) as QTR_NUM  , it should give you an incremental series of numbers

20164  =1

20171 = 2

20172 = 3

20173 = 4

20174 = 5  and so on



then current Quarter  = Max(QTR_NUM)

previous Quarter = Max(QTR_NUM)-1


pooja821
New Contributor III

Re: finding previous quarter from recent

but then id hampers automation I have to manually add quarter_num for future quarters

vinieme12
Esteemed Contributor II

Re: finding previous quarter from recent

why?

Community Browser