Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
Here is the app..
What is the required sorting?
FYs.... then FY with Q?
Try this
RangeSum(Right(Fiscal_Year, 2), Only({1} Dim)/1e2, FYearMonth/1e10)