Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a date field (PRIMARY_DC_DATE) , format, DD/MMM/YYYY, in which I need to show the current Fiscal and Quarter along with the following quarters by sum of PIPE_REVENUE in a chart.
Example data with fiscal and qtr already transformed using PRIMARY_DC_DATE below.
Fiscal Year | QTR | Overall Status | Sum Pipe Revenue |
FY17 | Q1 | HIGH | 11.96 |
FY17 | Q1 | MEDIUM | 76.16 |
FY17 | Q1 | LOW | 122.34 |
FY17 | Q2 | HIGH | 100.05 |
FY17 | Q2 | MEDIUM | 112.47 |
FY17 | Q2 | LOW | 240.36 |
FY17 | Q3 | HIGH | 30.72 |
FY17 | Q3 | MEDIUM | 307.13 |
FY17 | Q3 | LOW | 136.78 |
FY17 | Q4 | HIGH | 29.58 |
FY17 | Q4 | MEDIUM | 136.31 |
FY17 | Q4 | LOW | 236.31 |
FY18 | Q1 | HIGH | 42.58 |
FY18 | Q1 | MEDIUM | 159.31 |
FY18 | Q1 | LOW | 286.31 |
Since current fiscal is FY17 Q2 (we are using July-Jun for fiscal months). I want to show FY17 Q2,Q3,Q4 & FY18 Q1. The attachment show the end result. Your help is much appreciated. thanks
I agree with your second screen shot on the combined dimensions - looks good!
Something like this?
Yes - that appears to be what I need - please advise how I can achieve this. thanks!
I had to create a new field because you did not share your date field with us, but a date field can also work for you
Table:
LOAD *,
Date(Date#(Num((KeepChar(QTR, '0123456789') * 300) + (KeepChar([Fiscal Year], '0123456789') * 1), '0000'), 'MMYY'), 'MM-YYYY') as [Fiscal Month Year];
LOAD * INLINE [
Fiscal Year, QTR, Overall Status, Sum Pipe Revenue
FY17, Q1, HIGH, 11.96
FY17, Q1, MEDIUM, 76.16
FY17, Q1, LOW, 122.34
FY17, Q2, HIGH, 100.05
FY17, Q2, MEDIUM, 112.47
FY17, Q2, LOW, 240.36
FY17, Q3, HIGH, 30.72
FY17, Q3, MEDIUM, 307.13
FY17, Q3, LOW, 136.78
FY17, Q4, HIGH, 29.58
FY17, Q4, MEDIUM, 136.31
FY17, Q4, LOW, 236.31
FY18, Q1, HIGH, 42.58
FY18, Q1, MEDIUM, 159.31
FY18, Q1, LOW, 286.31
];
and once I have this new field, I used it in my set analysis to show last 4 quarters
=Sum({<[Fiscal Month Year] = {"$(='>' & Date(AddMonths(Max([Fiscal Month Year]), -12), 'MM-YYYY') & '<=' & Date(Max([Fiscal Month Year]), 'MM-YYYY'))"}>}[Sum Pipe Revenue])
I was unable to get similar results. Perhaps not providing the date values in initial thread is the issue. Here is the raw data with PRIMARY_DC_DATE. Can you import into your app to see if the results are the same? Also in the chart how could I achieve two dimensions instead of having Q2FY17.......
......show the following
Q2, Q3, Q4, Q1
FY17 FY18
Snippet of Raw Data
Primary DC Ship Date | Overall Status | Pipe Revenue (000s) |
07-Nov-2016 | LOW | 557 |
07-Aug-2017 | LOW | 167 |
05-Sep-2016 | LOW | 18 |
05-Sep-2016 | MEDIUM | 21 |
05-Dec-2016 | LOW | - |
05-Dec-2016 | LOW | 495 |
02-Jan-2017 | MEDIUM | 211 |
06-Feb-2017 | LOW | 140 |
05-Jun-2017 | MEDIUM | 1.1 |
05-Jun-2017 | MEDIUM | 268 |
04-Dec-2017 | LOW | 27 |
02-Jan-2017 | LOW | 20 |
02-Jan-2017 | MEDIUM | 182 |
04-Dec-2017 | LOW | 727 |
04-Jun-2018 | LOW | - |
03-Dec-2018 | LOW | - |
07-Aug-2017 | MEDIUM | 396 |
06-Nov-2017 | HIGH | 408 |
04-Dec-2017 | LOW | - |
12-Jun-2017 | LOW | 168 |
02-Jul-2018 | HIGH | - |
06-Nov-2017 | HIGH | 1877 |
03-Jul-2017 | MEDIUM | 0 |
03-Jul-2017 | HIGH | 978 |
16-Jan-2017 | MEDIUM | - |
06-Mar-2017 | LOW | 75 |
05-Dec-2016 | LOW | - |
05-Jun-2017 | MEDIUM | - |
07-Nov-2016 | LOW | 1080 |
19-Dec-2016 | LOW | - |
06-Feb-2017 | LOW | - |
06-Feb-2017 | LOW | 568 |
07-Aug-2017 | MEDIUM | - |
30-Jan-2017 | MEDIUM | - |
03-Jul-2017 | MEDIUM | 112 |
05-Dec-2016 | LOW | 570 |
06-Mar-2017 | LOW | 461.1 |
06-Feb-2017 | LOW | 789 |
08-May-2017 | MEDIUM | 1644 |
06-Feb-2017 | LOW | 1 |
06-Feb-2017 | LOW | 1683 |
06-Feb-2017 | LOW | - |
05-Jun-2017 | LOW | 3845 |
03-Jul-2017 | HIGH | 5000 |
06-Feb-2017 | LOW | 795 |
04-Sep-2017 | HIGH | 10759 |
05-Jun-2017 | MEDIUM | 1694 |
07-Aug-2017 | LOW | 402 |
07-Aug-2017 | MEDIUM | 8589 |
07-Aug-2017 | MEDIUM | 3772 |
07-Aug-2017 | LOW | 1151 |
04-Sep-2017 | MEDIUM | 7649 |
15-May-2017 | LOW | - |
15-May-2017 | MEDIUM | - |
03-Jul-2017 | HIGH | 2809 |
07-Aug-2017 | HIGH | 362 |
07-Aug-2017 | LOW | 436 |
07-Aug-2017 | LOW | - |
01-May-2017 | LOW | - |
01-May-2017 | LOW | - |
01-May-2017 | LOW | - |
07-Aug-2017 | MEDIUM | - |
03-Jul-2017 | LOW | - |
07-Aug-2017 | LOW | - |
07-Aug-2017 | LOW | - |
07-Aug-2017 | MEDIUM | 2075 |
06-Mar-2017 | LOW | 484 |
03-Jul-2017 | MEDIUM | 3445 |
03-Jul-2017 | MEDIUM | 274.5 |
06-Mar-2017 | MEDIUM | 400 |
01-May-2017 | LOW | - |
05-Jun-2017 | LOW | 2319 |
07-Aug-2017 | HIGH | 2319 |
03-Jul-2017 | HIGH | - |
02-Oct-2017 | LOW | 2340 |
01-Jan-2018 | LOW | 1009 |
02-Oct-2017 | LOW | 2239 |
05-Mar-2018 | LOW | 296 |
20-Mar-2017 | HIGH | 630 |
05-Jun-2017 | - | - |
05-Jun-2017 | MEDIUM | - |
02-Oct-2017 | MEDIUM | - |
13-Mar-2017 | LOW | - |
03-Jul-2017 | LOW | 240.3 |
06-Nov-2017 | LOW | 2871 |
04-Sep-2017 | LOW | - |
02-Jul-2018 | MEDIUM | - |
02-Jul-2018 | LOW | 2047 |
06-Nov-2017 | LOW | - |
05-Feb-2018 | LOW | - |
06-Nov-2017 | LOW | - |
06-Nov-2017 | LOW | 2875 |
05-Feb-2018 | LOW | 2286 |
05-Feb-2018 | LOW | 1191 |
05-Feb-2018 | LOW | 791 |
05-Feb-2018 | LOW | 988 |
04-Dec-2017 | LOW | - |
04-Sep-2017 | LOW | - |
04-Sep-2017 | MEDIUM | 580 |
03-Jul-2017 | MEDIUM | - |
06-Mar-2017 | HIGH | - |
04-Jun-2018 | LOW | - |
06-Aug-2018 | LOW | 1788 |
04-Jun-2018 | LOW | - |
02-Jul-2018 | LOW | 1211 |
06-Aug-2018 | LOW | - |
02-Jul-2018 | LOW | - |
03-Sep-2018 | LOW | - |
04-Sep-2017 | LOW | 0 |
04-Sep-2017 | LOW | 0 |
04-Sep-2017 | LOW | 0 |
03-Jul-2017 | MEDIUM | - |
01-Jan-2018 | LOW | - |
04-Jun-2018 | - | - |
04-Sep-2017 | - | - |
24-Apr-2017 | - | - |
03-Jul-2017 | HIGH | - |
27-Mar-2017 | MEDIUM | 278 |
06-Feb-2017 | MEDIUM | 3623 |
10-Oct-2016 | MEDIUM | 3276 |
21-Nov-2016 | LOW | 1192 |
02-Jan-2017 | LOW | 1784 |
21-Nov-2016 | LOW | 1391 |
07-Nov-2016 | MEDIUM | 892 |
07-Nov-2016 | MEDIUM | 1169 |
10-Oct-2016 | LOW | 466 |
18-Sep-2017 | MEDIUM | 5601 |
17-Jul-2017 | LOW | 13444 |
10-Oct-2016 | LOW | - |
17-Apr-2017 | MEDIUM | 3305 |
10-Oct-2016 | HIGH | 287 |
07-May-2018 | LOW | 521 |
02-Jan-2017 | LOW | 1117 |
25-Sep-2017 | LOW | 709 |
08-Jan-2018 | MEDIUM | 3442 |
07-Nov-2016 | LOW | 2930 |
18-Jun-2018 | LOW | 692 |
25-Sep-2017 | LOW | 17421 |
18-Dec-2017 | LOW | 717 |
27-Mar-2017 | MEDIUM | 3123 |
26-Jun-2016 | LOW | 1549 |
12-Jun-2017 | LOW | 942 |
25-Sep-2017 | MEDIUM | 846 |
10-Dec-2018 | MEDIUM | 1283 |
02-Oct-2017 | LOW | - |
20-Nov-2017 | LOW | 362 |
12-Dec-2016 | LOW | - |
20-Nov-2017 | LOW | - |
10-Apr-2017 | LOW | - |
18-Jun-2018 | MEDIUM | 501 |
07-May-2018 | LOW | 2988 |
23-Oct-2017 | LOW | 911 |
12-Jun-2017 | MEDIUM | 2226 |
28-Oct-2018 | LOW | 2302 |
27-Nov-2017 | MEDIUM | 2337 |
09-Oct-2017 | LOW | - |
09-Oct-2017 | LOW | 5833 |
06-Nov-2017 | LOW | 1366 |
22-Jan-2018 | LOW | 1434 |
23-Jan-2017 | LOW | - |
18-Jun-2018 | LOW | 140 |
04-Dec-2017 | LOW | 648 |
07-May-2018 | LOW | 660 |
06-Nov-2017 | MEDIUM | 109 |
26-Mar-2018 | LOW | 360 |
05-Nov-2018 | MEDIUM | 725 |
07-May-2018 | LOW | 6592 |
23-Jul-2018 | LOW | 3473 |
18-Jun-2018 | LOW | 1140 |
18-Jun-2018 | LOW | 464 |
08-Oct-2018 | LOW | - |
09-Jul-2018 | LOW | 2494 |
03-Jul-2017 | LOW | - |
06-Nov-2017 | LOW | - |
18-Jun-2018 | LOW | 214 |
06-Nov-2017 | MEDIUM | 869 |
22-Oct-2018 | LOW | 825 |
09-Jul-2018 | LOW | 3379 |
22-Jan-2018 | LOW | - |
26-Mar-2018 | LOW | - |
24-Sep-2018 | LOW | - |
09-Jul-2018 | LOW | - |
05-Nov-2018 | LOW | - |
18-Jun-2018 | LOW | 1341 |
28-Jan-2019 | MEDIUM | 995 |
03-Jul-2017 | LOW | - |
24-Sep-2018 | LOW | - |
02-Jul-2018 | MEDIUM | 3131 |
19-Nov-2018 | LOW | 373 |
26-Nov-2018 | LOW | - |
27-Nov-2017 | LOW | - |
19-Jun-2017 | LOW | - |
05-Nov-2018 | LOW | - |
01-Dec-2016 | LOW | 239 |
06-Feb-2017 | MEDIUM | 90.4 |
07-Nov-2016 | MEDIUM | 1755 |
02-Jul-2018 | LOW | - |
03-Jul-2017 | MEDIUM | 1657 |
05-Dec-2016 | LOW | 1001 |
07-Nov-2016 | MEDIUM | 5312 |
05-Sep-2016 | LOW | - |
04-Jul-2016 | LOW | 337 |
05-Dec-2016 | LOW | 32511 |
02-Jan-2017 | LOW | 7759 |
03-Oct-2016 | HIGH | 70849 |
03-Apr-2017 | MEDIUM | 2165 |
17-Oct-2016 | LOW | 28 |
03-Apr-2017 | MEDIUM | 2676 |
02-Jan-2017 | LOW | - |
06-Mar-2017 | MEDIUM | 1635.6 |
13-Feb-2017 | LOW | 187.8 |
02-Jan-2017 | LOW | 6964 |
03-Apr-2017 | MEDIUM | 1635.6 |
02-Jan-2017 | LOW | 2165 |
06-Feb-2017 | HIGH | 12275 |
03-Apr-2017 | MEDIUM | 2843 |
03-Jul-2017 | HIGH | 13740 |
05-Dec-2016 | LOW | 4800 |
02-Jan-2017 | LOW | 208 |
05-Jun-2017 | MEDIUM | 1099 |
07-Aug-2017 | MEDIUM | 5400 |
02-Jan-2017 | HIGH | 26 |
05-Jun-2017 | LOW | 4153 |
06-Feb-2017 | HIGH | - |
05-Jun-2017 | MEDIUM | - |
04-Dec-2017 | HIGH | 1361 |
03-Apr-2017 | HIGH | 12 |
05-Jun-2017 | LOW | 5383 |
23-Dec-2016 | LOW | - |
23-Dec-2016 | LOW | - |
06-Nov-2017 | MEDIUM | 11426 |
03-Apr-2017 | HIGH | 3043 |
05-Jun-2017 | HIGH | 3794 |
05-Jun-2017 | HIGH | 825 |
05-Jun-2017 | HIGH | 432 |
03-Jul-2017 | MEDIUM | 1695 |
05-Jun-2017 | MEDIUM | 354 |
05-Jun-2017 | MEDIUM | 257 |
02-Jul-2018 | MEDIUM | - |
04-Dec-2017 | MEDIUM | - |
05-Mar-2018 | HIGH | 3856 |
02-Apr-2018 | MEDIUM | 1433 |
06-Aug-2018 | MEDIUM | - |
06-Nov-2017 | MEDIUM | 12555 |
02-Jan-2017 | MEDIUM | 464 |
02-Jan-2017 | HIGH | 464 |
02-Jan-2017 | MEDIUM | 464 |
05-Jun-2017 | LOW | 159 |
01-Jan-2018 | HIGH | 2125 |
05-Jun-2017 | LOW | 2215 |
02-Oct-2017 | MEDIUM | 891 |
03-Apr-2017 | HIGH | - |
02-Jul-2018 | MEDIUM | 3989 |
02-Oct-2017 | LOW | - |
05-Feb-2018 | MEDIUM | - |
04-Jun-2018 | MEDIUM | - |
01-Jan-2018 | MEDIUM | - |
04-Dec-2017 | MEDIUM | - |
01-Jan-2018 | MEDIUM | - |
01-Jan-2018 | MEDIUM | - |
02-Oct-2017 | MEDIUM | - |
04-Jun-2018 | LOW | - |
04-Jun-2018 | MEDIUM | - |
02-Oct-2017 | LOW | 2483 |
02-Oct-2017 | MEDIUM | 306 |
02-Oct-2017 | LOW | - |
04-Dec-2017 | LOW | 1162 |
01-Jan-2018 | LOW | - |
02-Apr-2018 | LOW | - |
01-Oct-2018 | LOW | - |
04-Jun-2018 | LOW | - |
03-Sep-2018 | LOW | - |
04-Jun-2018 | LOW | - |
What is your fiscal year start? Can you share the script you use to create Fiscal Yea and Quarters?
Actually the fiscal year is calculated in Oracle using the PRIMARY_DC_DATE field however I can convert quarter and year in the following manner and avoid the Oracle converted field to make things easier.
If(Match(Month(PRIMARY_DC_DATE),'Jul','Aug','Sept') > 0 ,'FYQ1',
If(Match(Month(PRIMARY_DC_DATE),'Oct','Nov','Dec') > 0 , 'FYQ2',
If(Match(Month(PRIMARY_DC_DATE),'Jan','Feb','Mar') > 0 ,'FYQ3','FYQ4'))) as PRIMARY_DC_DATE_QUARTER,
If(month(PRIMARY_DC_DATE)<=6,year(PRIMARY_DC_DATE),year(PRIMARY_DC_DATE)+1) as PRIMARY_DC_DATE_FISCAL_YEAR
BTW thanks so much for your help thus far - really appreciate it.
If I leave two dimensions, I get this
But you can combine your dimensions like this
I agree with your second screen shot on the combined dimensions - looks good!
Great ... so we are all good then?