Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
rothtd
Contributor III

YTD expression referencing a dimension value?

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:

YearWeekYTD
2019798
2019681
2019565
2019450

 

My data:

YearWeekValue
2019717
2019616
2019515
2019414
2019313
2019212
2019111
201852152
201851151
201850150
201849149
Labels (1)
1 Solution

Accepted Solutions
pradosh_thakur
Honored Contributor II

Re: YTD expression referencing a dimension value?

Do your YTD calculation as usual using rangseum(above(sum(Value),0,rowno()))

and then multiply it by avg({<Week={"=Week>=$(=Max(Week)-3)"}>}1)
Learning never stops.
3 Replies
pradosh_thakur
Honored Contributor II

Re: YTD expression referencing a dimension value?

Do your YTD calculation as usual using rangseum(above(sum(Value),0,rowno()))

and then multiply it by avg({<Week={"=Week>=$(=Max(Week)-3)"}>}1)
Learning never stops.
rothtd
Contributor III

Re: YTD expression referencing a dimension value?

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.

 

rothtd
Contributor III

Re: YTD expression referencing a dimension value?

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.