Cumulative line graph with month and multiple year dimensions

[summer]

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????

[Ezir]

Hi Summer.

Can you show examples of data/table?

Regards

Ezir

[summer]

FiscalMonthName	FY Expensed	Total Fund Amount Expensed
Oct	2017	6287502.12
Oct	2018	12888216.95
Oct	2019	17385355.59
Nov	2017	7430771.31
Nov	2018	12407995.28
Nov	2019	15046899.75
Dec	2017	11862961.56
Dec	2018	10955897.31
Dec	2019	13538886.25
Jan	2017	20529593.6
Jan	2018	15945021.87
Jan	2019	20378532.66
Feb	2017	11107382.31
Feb	2018	11750715.84
Feb	2019	18328350.5
Mar	2017	10195723.25
Mar	2018	18738538.13
Mar	2019	16247621.61
Apr	2017	13397698.81
Apr	2018	14768607.73
Apr	2019	19888942.23
May	2017	10556563.56
May	2018	18799792.02
May	2019	16274785.08
Jun	2017	15206316.27
Jun	2018	14508782.15
Jun	2019	22783665.32
Jul	2017	14453351.6
Jul	2018	16997244.34
Jul	2019	20728210.23
Aug	2017	16671257
Aug	2018	20628299.16
Aug	2019	10928117.06
Sep	2017	13514305.45
Sep	2018	18299679.84

[Ezir]

Hi @summer,

If the result below is expected:

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 

[summer]

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

[Ezir]

Hi @summer,

A suggestion...

1) Add a combo chart

2) 

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:

Regards

[AimHigh2020]

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??

[AimHigh2020]

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.

[JanArnold]

Does anyone have a solution for this?

[Diinkant]

Any solution for this?