Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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????
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 |
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
Hi and thanks so much for replying. I am actually looking for the following format with a separate line for each year.
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
Hi @Ezirraffner
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??
Hi @Ezirraffner
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.
Does anyone have a solution for this?
Any solution for this?