Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a field Month in my source data. My task is to generate a new feld named Quarter based on the Month field.
So, while loading data I'd like to set the following condition:
- for values Jan, Feb, Mar generate value Q1 in the Quarter field
- for values Apr, May, Jun generate value Q2 in the Quarter field
- for values Jul, Aug, Sep generate value Q3 in the Quarter field
- for values Oct, Nov, Dec generate value Q4 in the Quarter field
Which operators should I use to implement it?
Thank you in advance,
Larisa
See attached example.
Hi Larisa,
First, you need to convert the Month field from alphanumeric string to a number. You can do it in a number of ways. One of them is this:
Match(Month, 'Jan', 'Feb', 'Mar', ..., 'Dec')
Then, use this commonly used calculation of the Quarter:
'Q' & ceil(MonthNumber/3) as Quarter.
cheers,
Oleg Troyansky
www.masterssummit.com - take you QlikView skills to the next level!
Gysbert suggests
'Q' & ceil(month(Month)/3) as Quarter
I would do it slightly more elaborate (to get numeric values):
Dual('Q' & ceil(month(Month)/3), ceil(month(Month)/3)) as Quarter;
If you want sequential quarters, you can use QuarterName()
HIC