QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
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:

 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
Labels (1)
• chart expressions

1 Solution

Accepted Solutions
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
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.
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.

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.