Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
venkatbza
Creator
Creator

Need to show Quarter inside Yearmonth column

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

1 Solution

Accepted Solutions
Kushal_Chawda

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

View solution in original post

4 Replies
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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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

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

View solution in original post

Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.