Need to show Quarter inside Yearmonth column

[venkatbza]

Hi Friends,

I want to show Quarter inside year month column, below is the example.

I have a Data like this

Month-Year

Jan-18

Feb-18

Mar-18

Apr-18

May-18

Jun-18

Jul-18

Aug-18

Sep-18

Oct-18

Nov-18

Dec-18

Jan-19

Feb-19

Mar-19

Apr-19

May-19

Jun-19

Jul-19

Aug-19

Sep-19

Oct-19

Nov-19

Dec-19

I need to show output like below, quarter need to show  for every 3 months

Month-Year

Jan-18

Feb-18

Mar-18

Q1-2018

Apr-18

May-18

Jun-18

Q2-2018

Jul-18

Aug-18

Sep-18

Q3-2018

Oct-18

Nov-18

Dec-18

Q4-2018

Jan-19

Feb-19

Mar-19

Q1-2019

Apr-19

May-19

Jun-19

Q2-2019

Jul-19

Aug-19

Sep-19

Q3-2019

Oct-19

Nov-19

Dec-19

Q4-2019

Thanks,

Venkat

[Anil_Babu_Samineni]

May be

LOAD MonthName(Date#([Month-Year], 'MMM-YY')) as [Month-Year] From Table;

Outer Join

LOAD 'Q'& Ceil(Month([Month-Year])/3) & '-' & Year([Month-Year]) as [Month-Year] Resident Sample;

[Kushal_Chawda]

You can do something like below

Data:
LOAD 
     "Month-Year",
     year(date#("Month-Year",'MMM-YY'))&num(month(date#("Month-Year",'MMM-YY')),'00') as MonthNum
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

Concatenate(Data)
load  'Q' & Ceil (month(date#("Month-Year",'MMM-YY'))/3)&'-'&Year(date#("Month-Year",'MMM-YY')) as "Month-Year",
Year(date#("Month-Year",'MMM-YY'))&num(Ceil(month(date#("Month-Year",'MMM-YY'))/3)*3,'00')+0.5  as  MonthNum
Resident Data;

Note:

1) When you use Month-Year field anywhere make sure that you sort it by MonthNum field

2) I have used Date# function because Month-Year was in text format while reading the data. If your Month-Year filed is already in Number format , no need to use Date# function

[Kushal_Chawda · Accepted solution]

If you want your month year field to work on Quarter selection, you need to create bridge table

MonthYear:
LOAD 
     "Month-Year" ,
     year(date#("Month-Year",'MMM-YY'))&num(month(date#("Month-Year",'MMM-YY')),'00') as MonthNum,
     1 as Flag
FROM [lib://Qlik web]
(html, utf8, embedded labels, table is @1);

Concatenate(MonthYear)
load  'Q' & Ceil (month(date#("Month-Year",'MMM-YY'))/3)&'-'&Year(date#("Month-Year",'MMM-YY')) as "Month-Year",
       Year(date#("Month-Year",'MMM-YY'))&num(Ceil(month(date#("Month-Year",'MMM-YY'))/3)*3,'00')+0.5  as  MonthNum,
       2 as Flag
Resident MonthYear;

Bridge:
load  'Q' & Ceil (month(date#("Month-Year",'MMM-YY'))/3)&'-'&Year(date#("Month-Year",'MMM-YY')) as "Month-Year",
      "Month-Year" as ActaulMonthYear
Resident MonthYear;

Concatenate(Bridge)
load  "Month-Year",
      "Month-Year" as ActaulMonthYear
Resident MonthYear
where Flag=1;

Now you can link ActaulMonthYear field to Data either directly(link to Month-Year field) or via Calendar

[Brett_Bleess]

Venkat, did either of the posts help you with things?  If so, do not forget to return to the thread and use the Accept as Solution button to mark any posts that did help you solve the use case, as this gives them credit for the help, and it lets the other Community Members know what worked.  If you are still working on things, leave an update post.

Regards,
Brett