Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Experts - I am trying to make a Year-to-date straight table chart for only the most recent 4 weeks (weeks 4-7 as of today) , where YTD for week 7 should be the sum of weeks 1 through 7 (17+16+15+14+13+12+11 = 98) and week 6 should be the sum of weeks 1 through 6 (16+15+14+13+12+11=81) and week 5 should be the sum of weeks 1 through 5 (15+14+13+12+11=65)., etc. I don't want to show anything more in the chart then the last 4 weeks (so I don't think a chart accumulation would work) and I don't want the number to change based on column sort order (so I'm hesitant to use chart inter-record functions). I'm thinking it would be some sort of nested set expression, perhaps with a aggr() - but I'm stumped. I would like to simply say YTD = sum(>=1 <=Week), where Week represents the value of the particular week dimension for the given row - but I don't know how to code this type of expression.
Thoughts? How would you do this?
Chart I'm trying to make:
Year | Week | YTD |
2019 | 7 | 98 |
2019 | 6 | 81 |
2019 | 5 | 65 |
2019 | 4 | 50 |
My data:
Year | Week | Value |
2019 | 7 | 17 |
2019 | 6 | 16 |
2019 | 5 | 15 |
2019 | 4 | 14 |
2019 | 3 | 13 |
2019 | 2 | 12 |
2019 | 1 | 11 |
2018 | 52 | 152 |
2018 | 51 | 151 |
2018 | 50 | 150 |
2018 | 49 | 149 |
Thanks, but as I noted, I don't think I can use RangeSum(). I created my sample data only for purposes of explaining my issue, but my real issue is that Value isn't a number but an expression (A+B+C / A+C+D+E) = X%, and given the division I can just "sum" the resultants, but rather I need to reevaluate the expression each time. Also, this idea is dependent on the chart being sorted in a specific order - which I think I could have lived with by not allowing the user interactive sorting.
Thanks for your suggestion.
After some research, set analysis won't work since set expressions are evaluated at the chart level, not the row level. I tweaked your suggestion to come up with what I need in my case:
RangeSum(Below(Sum({$ <[Year]={'2019'}>} Value), 0, NOOfRows()))
** 2019 will be replaced with a variable
Thanks for your help!
** I am still interested in how to do this with an IF-statement/aggr() where I can reference the current rows value of the dimension in the if-statement.