How to create yearly sales given the quarterly numbers

[narayanamsn]

Hi,

Have a below scenario. I have quarterly sales numbers and and want to get yearly sales as well in the summary table.

I am not allowed to create a column for the year as I am using some functions - Pick , Dim to get the Qtr with Previous Qtr, Quarter with the same Quarter of previous Year comparisons.

Base Data :

Code	Qtr	sales
a	Q1'18	37
b	Q1'18	76
a	Q2'18	99
b	Q2'18	33
a	Q3'18	79
b	Q3'18	42
a	Q4'18	85
b	Q4'18	82
a	Q1'17	27
b	Q1'17	67
a	Q2'17	48
b	Q2'17	56
a	Q3'17	21
b	Q3'17	26
a	Q4'17	69
b	Q4'17	85
a	Q1'18	69
b	Q1'18	45
a	Q2'18	86
b	Q2'18	89
a	Q3'18	86
b	Q3'18	79
a	Q4'18	38
b	Q4'18	35
a	Q1'17	67
b	Q1'17	56
a	Q2'17	53
b	Q2'17	11
a	Q3'17	63
b	Q3'17	22
a	Q4'17	81
b	Q4'17	100

Expected table:

Code	Q1'17	Q2'17	Q3'17	Q4'17	FY17	Q1'18	Q2'18	Q3'18	Q4'18	FY18
a	94	101	84	150	429	106	185	165	123	579
b	123	67	48	185	423	121	122	121	117	481

stalwar1‌

[sunny_talwar]

Would you be able to share a sample qvw, it will be easier to see and play around with. The problem is that there is not straight forward way to do this and will have to be played around based on the data you have and the sorting you need.

[narayanamsn]

Here is the app..

[sunny_talwar]

What is the required sorting?

FYs.... then FY with Q?

[narayanamsn]

[sunny_talwar]

Try this

RangeSum(Right(Fiscal_Year, 2), Only({1} Dim)/1e2, FYearMonth/1e10)