convert date , year and month to quarter

[srujanaponnuru]

Hi,

Please hel p me out wiht the below question.

I have data as shown below:

COB_Date

01/01/2015

02/01/2015

03/01/2015

04/01/2015

05/01/2015

06/01/2015

07/01/2015

08/01/2015

09/01/2015

01/01/2015

02/01/2015

03/01/2015

04/01/2015

05/01/2015

06/01/2015

07/01/2015

08/01/2015

09/01/2015

01/01/2015

02/01/2015

03/01/2015

04/01/2015

05/01/2015

06/01/2015

07/01/2015

08/01/2015

09/01/2015

I need my x-axes to be in quarter based like Q1 2015, Q2 2015, Q3 2015 in the bar chart.

please help me out.

[MK_QSL]

Create QuarterYear in Script from Date

'Q' &Ceil(Month(COB_Date)/3) & ' ' &Year(COB_Date) as QuarterYear,

[sunny_talwar · Accepted solution]

Here is an example of how you can do it in the script (as prescribed by Manish Kachhia‌) or if you don't want to somehow do it in the script, I have done it on the front end using a calculated dimensions also:

Script:

Table:

LOAD *,

  Dual('Q' & Ceil(Month(COB_Date)/3) & '-' & Year(COB_Date), QuarterName(COB_Date)) as QuarterYear

Inline [

COB_Date, Sales

01/01/2015, 20

02/01/2015, 30

03/01/2015, 68

04/01/2015, 25

05/01/2015, 65

06/01/2015, 75

07/01/2015, 25

08/01/2015, 98

09/01/2015, 45

01/01/2015, 36

02/01/2015, 69

03/01/2015, 26

04/01/2015, 65

05/01/2015, 75

06/01/2015, 82

07/01/2015, 92

08/01/2015, 75

09/01/2015, 73

01/01/2015, 26

02/01/2015, 24

03/01/2015, 12

04/01/2015, 64

05/01/2015, 68

06/01/2015, 94

07/01/2015, 93

08/01/2015, 84

09/01/2015, 71

];

I have done QuarterYear in Preceding Load, but you should do it in your calendar object where you are doing all other date manipulations.

I then created two line charts one using the field I created QuarterYear as dimension and other one uses a calculated dimension.

Chart 1:

Dimension -> QuarterYear

Expression -> Sum(Sales)

Output:

Chart 2:

Dimension -> ='Q' & Ceil(Month(COB_Date)/3) & '-' & Year(COB_Date)

Expression - > Sum(Sales)

Output:

I hope this will help you out. Attaching the sample qvw for reference.

Best,

Sunny

[srujanaponnuru]

It worked, Thank you so much to all of you

[srujanaponnuru]

Thank you , it worked

[MK_QSL]

Kindly close the thread by selecting Correct/Helpful answer so that others having same problems can use this solution.

[MK_QSL]

You selected your answer as correct !!!

[sunny_talwar]

Its awesome that one of the recommended solution worked for you, but I suggest that instead of marking your own answer as correct, you should try to reward others who have tried to help you. Whoever's answer worked for you, mark there answer as correct answer and use helpful answers to reward others who you think were close.

Best,

Sunny

[sunny_talwar]

Thank you Srujana‌ for listening to my advice.

Best,

Sunny