topic Re: Previous Quarter Number using Mapping Load (Not working- December) in QlikView

Previous Quarter Number using Mapping Load (Not working- December)

helen_pip — Wed, 08 Jan 2014 14:07:53 GMT

Hello

I was wondering if someone could kindly help me

I have a QVW and using a MAPPING LOAD I load in a spreadsheet so I can obtain the Current Quarter according to the month number

The spreadsheet data looks like this:

Month	Quarter
1	4
2	4
3	4
4	1
5	1
6	1
7	2
8	2
9	2
10	3
11	3
12	3

By using apply Map I am able to retrieve the current quarter

applymap('Quarters_Map', num(month(Today())), null()) AS Current_Quarter

I also have a 2nd field where I can retrieve the Quarter for the previous month. In 2013 this worked perfectly, however now we have moved into 2014, the following line of code now returns either a 0 or nothing

applymap('Quarter_Name', MonthName(Today()),-1), null() AS Current_Quarter_Month_End

I am assuming that my mapping load is not working and cannot count back from 1 back to 12, so can anyone kindly help me retrieve the previous quarter number, from Today's date

Kind Regards

Helen

Re: Previous Quarter Number using Mapping Load (Not working- December)

maxgro — Wed, 08 Jan 2014 15:04:33 GMT

if I understand your question you can try this to calculate quarter and quarter of previous month

Table:

load

'Q' & ceil(month(d)/3) as QuarterCurrent,

'Q' & ceil(month(addmonths(monthstart(d),-1)) / 3) as QuarterPreviousMonth // -1 or -3 for your case

;

LOAD

floor(Today() + rand()*1000) as d

AutoGenerate

100;

Re: Previous Quarter Number using Mapping Load (Not working- December)

Wed, 08 Jan 2014 16:03:59 GMT

Hi Helen,

I Hope help this script,

LOAD *, 'Q' & Ceil(Month(Today())/3) AS Quarter1,'Q' & ceil(Month(addmonths(Today(),-1)) / 3) as Pre_Quarter1 Inline

[Quarter,Pre_Quarter

Quarter1,Pre_Quarter1];

Regards,

Raja.