Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrainford
Contributor III
Contributor III

How to show current FY/QTR and next 3 QTR's in bar chart

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 YearQTROverall StatusSum Pipe Revenue
FY17Q1HIGH11.96
FY17Q1MEDIUM76.16
FY17Q1LOW122.34
FY17Q2HIGH100.05
FY17Q2MEDIUM112.47
FY17Q2LOW240.36
FY17Q3HIGH30.72
FY17Q3MEDIUM307.13
FY17Q3LOW136.78
FY17Q4HIGH29.58
FY17Q4MEDIUM136.31
FY17Q4LOW236.31
FY18Q1HIGH42.58
FY18Q1MEDIUM159.31
FY18Q1LOW286.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

1 Solution

Accepted Solutions
mrainford
Contributor III
Contributor III
Author

I agree with your second screen shot on the combined dimensions - looks good!

View solution in original post

13 Replies
sunny_talwar

Something like this?

Capture.PNG

mrainford
Contributor III
Contributor III
Author

Yes - that appears to be what I need - please advise how I can achieve this. thanks!

sunny_talwar

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])

mrainford
Contributor III
Contributor III
Author

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 DateOverall StatusPipe Revenue (000s)
07-Nov-2016LOW557
07-Aug-2017LOW167
05-Sep-2016LOW18
05-Sep-2016MEDIUM21
05-Dec-2016LOW-
05-Dec-2016LOW495
02-Jan-2017MEDIUM211
06-Feb-2017LOW140
05-Jun-2017MEDIUM1.1
05-Jun-2017MEDIUM268
04-Dec-2017LOW27
02-Jan-2017LOW20
02-Jan-2017MEDIUM182
04-Dec-2017LOW727
04-Jun-2018LOW-
03-Dec-2018LOW-
07-Aug-2017MEDIUM396
06-Nov-2017HIGH408
04-Dec-2017LOW-
12-Jun-2017LOW168
02-Jul-2018HIGH-
06-Nov-2017HIGH1877
03-Jul-2017MEDIUM0
03-Jul-2017HIGH978
16-Jan-2017MEDIUM-
06-Mar-2017LOW75
05-Dec-2016LOW-
05-Jun-2017MEDIUM-
07-Nov-2016LOW1080
19-Dec-2016LOW-
06-Feb-2017LOW-
06-Feb-2017LOW568
07-Aug-2017MEDIUM-
30-Jan-2017MEDIUM-
03-Jul-2017MEDIUM112
05-Dec-2016LOW570
06-Mar-2017LOW461.1
06-Feb-2017LOW789
08-May-2017MEDIUM1644
06-Feb-2017LOW1
06-Feb-2017LOW1683
06-Feb-2017LOW-
05-Jun-2017LOW3845
03-Jul-2017HIGH5000
06-Feb-2017LOW795
04-Sep-2017HIGH10759
05-Jun-2017MEDIUM1694
07-Aug-2017LOW402
07-Aug-2017MEDIUM8589
07-Aug-2017MEDIUM3772
07-Aug-2017LOW1151
04-Sep-2017MEDIUM7649
15-May-2017LOW-
15-May-2017MEDIUM-
03-Jul-2017HIGH2809
07-Aug-2017HIGH362
07-Aug-2017LOW436
07-Aug-2017LOW-
01-May-2017LOW-
01-May-2017LOW-
01-May-2017LOW-
07-Aug-2017MEDIUM-
03-Jul-2017LOW-
07-Aug-2017LOW-
07-Aug-2017LOW-
07-Aug-2017MEDIUM2075
06-Mar-2017LOW484
03-Jul-2017MEDIUM3445
03-Jul-2017MEDIUM274.5
06-Mar-2017MEDIUM400
01-May-2017LOW-
05-Jun-2017LOW2319
07-Aug-2017HIGH2319
03-Jul-2017HIGH-
02-Oct-2017LOW2340
01-Jan-2018LOW1009
02-Oct-2017LOW2239
05-Mar-2018LOW296
20-Mar-2017HIGH630
05-Jun-2017--
05-Jun-2017MEDIUM-
02-Oct-2017MEDIUM-
13-Mar-2017LOW-
03-Jul-2017LOW240.3
06-Nov-2017LOW2871
04-Sep-2017LOW-
02-Jul-2018MEDIUM-
02-Jul-2018LOW2047
06-Nov-2017LOW-
05-Feb-2018LOW-
06-Nov-2017LOW-
06-Nov-2017LOW2875
05-Feb-2018LOW2286
05-Feb-2018LOW1191
05-Feb-2018LOW791
05-Feb-2018LOW988
04-Dec-2017LOW-
04-Sep-2017LOW-
04-Sep-2017MEDIUM580
03-Jul-2017MEDIUM-
06-Mar-2017HIGH-
04-Jun-2018LOW-
06-Aug-2018LOW1788
04-Jun-2018LOW-
02-Jul-2018LOW1211
06-Aug-2018LOW-
02-Jul-2018LOW-
03-Sep-2018LOW-
04-Sep-2017LOW0
04-Sep-2017LOW0
04-Sep-2017LOW0
03-Jul-2017MEDIUM-
01-Jan-2018LOW-
04-Jun-2018--
04-Sep-2017--
24-Apr-2017--
03-Jul-2017HIGH-
27-Mar-2017MEDIUM278
06-Feb-2017MEDIUM3623
10-Oct-2016MEDIUM3276
21-Nov-2016LOW1192
02-Jan-2017LOW1784
21-Nov-2016LOW1391
07-Nov-2016MEDIUM892
07-Nov-2016MEDIUM1169
10-Oct-2016LOW466
18-Sep-2017MEDIUM5601
17-Jul-2017LOW13444
10-Oct-2016LOW-
17-Apr-2017MEDIUM3305
10-Oct-2016HIGH287
07-May-2018LOW521
02-Jan-2017LOW1117
25-Sep-2017LOW709
08-Jan-2018MEDIUM3442
07-Nov-2016LOW2930
18-Jun-2018LOW692
25-Sep-2017LOW17421
18-Dec-2017LOW717
27-Mar-2017MEDIUM3123
26-Jun-2016LOW1549
12-Jun-2017LOW942
25-Sep-2017MEDIUM846
10-Dec-2018MEDIUM1283
02-Oct-2017LOW-
20-Nov-2017LOW362
12-Dec-2016LOW-
20-Nov-2017LOW-
10-Apr-2017LOW-
18-Jun-2018MEDIUM501
07-May-2018LOW2988
23-Oct-2017LOW911
12-Jun-2017MEDIUM2226
28-Oct-2018LOW2302
27-Nov-2017MEDIUM2337
09-Oct-2017LOW-
09-Oct-2017LOW5833
06-Nov-2017LOW1366
22-Jan-2018LOW1434
23-Jan-2017LOW-
18-Jun-2018LOW140
04-Dec-2017LOW648
07-May-2018LOW660
06-Nov-2017MEDIUM109
26-Mar-2018LOW360
05-Nov-2018MEDIUM725
07-May-2018LOW6592
23-Jul-2018LOW3473
18-Jun-2018LOW1140
18-Jun-2018LOW464
08-Oct-2018LOW-
09-Jul-2018LOW2494
03-Jul-2017LOW-
06-Nov-2017LOW-
18-Jun-2018LOW214
06-Nov-2017MEDIUM869
22-Oct-2018LOW825
09-Jul-2018LOW3379
22-Jan-2018LOW-
26-Mar-2018LOW-
24-Sep-2018LOW-
09-Jul-2018LOW-
05-Nov-2018LOW-
18-Jun-2018LOW1341
28-Jan-2019MEDIUM995
03-Jul-2017LOW-
24-Sep-2018LOW-
02-Jul-2018MEDIUM3131
19-Nov-2018LOW373
26-Nov-2018LOW-
27-Nov-2017LOW-
19-Jun-2017LOW-
05-Nov-2018LOW-
01-Dec-2016LOW239
06-Feb-2017MEDIUM90.4
07-Nov-2016MEDIUM1755
02-Jul-2018LOW-
03-Jul-2017MEDIUM1657
05-Dec-2016LOW1001
07-Nov-2016MEDIUM5312
05-Sep-2016LOW-
04-Jul-2016LOW337
05-Dec-2016LOW32511
02-Jan-2017LOW7759
03-Oct-2016HIGH70849
03-Apr-2017MEDIUM2165
17-Oct-2016LOW28
03-Apr-2017MEDIUM2676
02-Jan-2017LOW-
06-Mar-2017MEDIUM1635.6
13-Feb-2017LOW187.8
02-Jan-2017LOW6964
03-Apr-2017MEDIUM1635.6
02-Jan-2017LOW2165
06-Feb-2017HIGH12275
03-Apr-2017MEDIUM2843
03-Jul-2017HIGH13740
05-Dec-2016LOW4800
02-Jan-2017LOW208
05-Jun-2017MEDIUM1099
07-Aug-2017MEDIUM5400
02-Jan-2017HIGH26
05-Jun-2017LOW4153
06-Feb-2017HIGH-
05-Jun-2017MEDIUM-
04-Dec-2017HIGH1361
03-Apr-2017HIGH12
05-Jun-2017LOW5383
23-Dec-2016LOW-
23-Dec-2016LOW-
06-Nov-2017MEDIUM11426
03-Apr-2017HIGH3043
05-Jun-2017HIGH3794
05-Jun-2017HIGH825
05-Jun-2017HIGH432
03-Jul-2017MEDIUM1695
05-Jun-2017MEDIUM354
05-Jun-2017MEDIUM257
02-Jul-2018MEDIUM-
04-Dec-2017MEDIUM-
05-Mar-2018HIGH3856
02-Apr-2018MEDIUM1433
06-Aug-2018MEDIUM-
06-Nov-2017MEDIUM12555
02-Jan-2017MEDIUM464
02-Jan-2017HIGH464
02-Jan-2017MEDIUM464
05-Jun-2017LOW159
01-Jan-2018HIGH2125
05-Jun-2017LOW2215
02-Oct-2017MEDIUM891
03-Apr-2017HIGH-
02-Jul-2018MEDIUM3989
02-Oct-2017LOW-
05-Feb-2018MEDIUM-
04-Jun-2018MEDIUM-
01-Jan-2018MEDIUM-
04-Dec-2017MEDIUM-
01-Jan-2018MEDIUM-
01-Jan-2018MEDIUM-
02-Oct-2017MEDIUM-
04-Jun-2018LOW-
04-Jun-2018MEDIUM-
02-Oct-2017LOW2483
02-Oct-2017MEDIUM306
02-Oct-2017LOW-
04-Dec-2017LOW1162
01-Jan-2018LOW-
02-Apr-2018LOW-
01-Oct-2018LOW-
04-Jun-2018LOW-
03-Sep-2018LOW-
04-Jun-2018LOW-
sunny_talwar

What is your fiscal year start? Can you share the script you use to create Fiscal Yea and Quarters?

mrainford
Contributor III
Contributor III
Author

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.

sunny_talwar

If I leave two dimensions, I get this

Capture.PNG

But you can combine your dimensions like this

Capture.PNG

mrainford
Contributor III
Contributor III
Author

I agree with your second screen shot on the combined dimensions - looks good!

sunny_talwar

Great ... so we are all good then?