Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Cumulative line graph with month and multiple year dimensions

I am trying unsuccessfully to create a cumulative line graph with month and multiple year dimensions.  The first year (2017) works correctly, but the second year (2018) and third year (2019) do not.  The first month (October) of the second year adds  October months 2017 and 2018 as the first month for the second year.  The third year adds the three months of 2017, 2018, 2019 as the first month.   I need each year to start over calculating the cumulative total.  The formula that I am using is  rangesum(Above(total sum(fund_exp_amount),0,rowno(total ))).  I have also tried  Aggr(RangeSum(Above(Sum(fund_exp_amount), 0, RowNo())), [FiscalYear], [FiscalMonthName]).  Do I need to do something different in the Data Load Editor????

7 Replies
Highlighted
Creator II
Creator II

Hi Summer.

Can you show examples of data/table?

Regards

Ezir
Highlighted
Contributor
Contributor

FiscalMonthNameFY ExpensedTotal Fund Amount Expensed
Oct20176287502.12
Oct201812888216.95
Oct201917385355.59
Nov20177430771.31
Nov201812407995.28
Nov201915046899.75
Dec201711862961.56
Dec201810955897.31
Dec201913538886.25
Jan201720529593.6
Jan201815945021.87
Jan201920378532.66
Feb201711107382.31
Feb201811750715.84
Feb201918328350.5
Mar201710195723.25
Mar201818738538.13
Mar201916247621.61
Apr201713397698.81
Apr201814768607.73
Apr201919888942.23
May201710556563.56
May201818799792.02
May201916274785.08
Jun201715206316.27
Jun201814508782.15
Jun201922783665.32
Jul201714453351.6
Jul201816997244.34
Jul201920728210.23
Aug201716671257
Aug201820628299.16
Aug201910928117.06
Sep201713514305.45
Sep201818299679.84
Highlighted
Creator II
Creator II

Hi @summer,

 

If the result below is expected:

linegraph.png

 

You would need to add calculated dimension:

 

=Date#(FiscalMonthName&'/'&FYExpensed,'MMM/YYYY')

 

 

Measure:

 

rangesum(Above(total sum(fund_exp_amount),0,rowno(total )))

 

 

I hope helps you.


Regards 

Highlighted
Contributor
Contributor

Hi and thanks so much for replying.  I am actually looking for the following format with a separate line for each year.  

Highlighted
Creator II
Creator II

Hi @summer,

 

A suggestion...

 

1) Add a combo chart

2) combchart.png

3) You can create variables for each year/line, see..

//You can create variables
//Line 2017
rangesum(Above(total sum({<FYExpensed={2017}>}Amount),0,rowno(total )))
//Line 2018
rangesum(Above(total sum({<FYExpensed={2018}>}Amount),0,rowno(total )))
//Line 2019
rangesum(Above(total sum({<FYExpensed={2019}>}Amount),0,rowno(total )))

 

Result:

workInPlace.png

Regards

Highlighted
Contributor
Contributor

Hi @Ezir 

Thank you for the alternative solution.   In my opinion, Qlik Sense should have provided the same functions like the ones in QlikView.  So many limitations and work around solutions I have to use in Qlik Sense.  That's sad.

 

Hi @summer 

Thank you for the good question. 🙂 Did you use this workaround solution? or find another way??

Highlighted
Contributor
Contributor

Hi @Ezir 

Question. 

I'm looking for a way to display only years users select.  Because those years are in the height of line, they are not responding to the users' selections.  I assume I need to program to hide/unhide the lines (year) based on users' selections.