Finding QoQ%

[#1]

Hi all

I have a straight table which contains something like this

Date Value

1-Nov 345

1OCt 456

1sept 123

1 Aug 123

I need to find to the QOQ% how can I do that

I tried the method to left join and add months and storing values.

Is there any good logic to do so.

[stephencredmond]

Hi,

If you load a Quarter dimension in your script then it is a straightforward matter to compare Quarters.

Quarters_Map:
Mapping Load * Inline [
M, Q
1,Q1
2,Q1
3,Q1
4,Q2
5,Q2
6,Q2
7,Q3
8,Q3
9,Q3
10,Q4
11,Q4
12,Q4
];
// Adjust the month-quarter link as required

MyDateData:
Load
...
Date,
ApplyMap('Quaters_Map', Month(Date)) As Quarter,
Year(Date) & '-' & ApplyMap('Quaters_Map', Month(Date)) As RollingQuarter,
...
;

Regards,

Stephen

[#3]

Use Date# function to convert your date as DDMMYYYY and then extract quarter as :

Quarter(Date#(date,'DDMMYYYY'))

This will give you QTR values.

[stephencredmond]

Hi Sunil,

How?

There is no Quarter function in QlikView. There is QuarterName, QuarterEnd, QuarterStart, but no Quarter.

Regards,

Stephen

[#5]

Instead of creating an entire mapping table just for quarter you can use the monthnumber divided by 3, rounded up to get the quarter. Comparing quarter to quarter can then be done using either set-analysis, flags marking previous quarters or if-expressions.

[stephencredmond]

Hi Jakob,

That only works if your financial year is Jan-Dec. Many companies do not.

The Mapping table is a more flexible way of doing this.

Regards,

Stephen