topic Re: YTD expression referencing a dimension value? in QlikView

YTD expression referencing a dimension value?

rothtd — Sat, 16 Nov 2024 04:36:38 GMT

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

Re: YTD expression referencing a dimension value?

pradosh_thakur — Wed, 30 Jan 2019 19:09:44 GMT

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)

Re: YTD expression referencing a dimension value?

rothtd — Wed, 30 Jan 2019 20:49:08 GMT

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.

Re: YTD expression referencing a dimension value?

rothtd — Thu, 31 Jan 2019 18:26:54 GMT

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.