Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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
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
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