Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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.