Get Current Dimension Value

[lblancher]

Hi All,

I am trying to figure out how to display a value that accumulates in a chart regardless of the selection. My table looks like this

Date Value

01/01/2000,5

01/01/2001,5

01/01/2002,2

01/01/2003,7

01/01/2004,9

01/01/2005,8

01/01/2006,6

01/01/2007,5

01/01/2008,1

01/01/2009,3

01/01/2010,4

01/01/2000,5

When I have not selected any date ranges, the Full Accumulation option works fine. But once I select a date range, the accumulation only works from the first value in the selections. I need the value of the accumulation to stay the same, I am just looking for the chart to show a smaller subset of the range. I was thinking I could use set analysis with something like sum(<$[Date] = {"<=CurrentDimensionValue"}>), but I'm not sure how to retrive the CurrentDimensionValue. Any suggestions?

Thanks,

Lucas Blancher

BizXcel Inc.

[johnw]

There's no way to reference the current dimension value in set analysis. In fact, the set is only calculated once, and for the entire chart.

Attached is what I consider to be a bad solution, but that may be better than no solution. Make a date island - the same dates, but disconnected from your data model. Use THAT date as the dimension. Then use an expression like sum(if("Date"<="Other Date",Value)). The big problem here is inefficiency. No problem for a sample like this, but could get ugly for a large data set. Another problem is usability, where selections in the chart aren't selecting the real data field, and vice versa. In the attached example, I try to address this a bit through conditional set analysis in the chart:

if(getselectedcount("Date")
,sum({<"Other Date"="Date","Date"=>} if("Date"<="Other Date","Value"))
,sum( if("Date"<="Other Date","Value")))

But selecting dates in the chart still won't affect OTHER charts, and things can still get a little funny. Another approach would be to ONLY let them select "Other Date", and do ALL of your connections via set analysis or IFs, but that can be a big maintenance headache.

Unfortunately, I don't have a better solution. I've tried half-heartedly in the past, and not succeeded. I say half-heartedly because I've never needed to do this for my own applications. It's just the kind of thing that should have a clean, simple and efficient solution, so I occasionally poke at it.

Hopefully someone else knows a good solution. I'd love to see it, even if it makes me feel stupid when I see how simple it is.

[tabletuner]

Dear John,

I have a chart. It has an expression with a 'year-week' field used in a set analysis to calculate the previous year-week values. Do i understand you correctly that QlikView doesnt allow this field 'year-week' to be used in the dimension of this very same chart?

If so, this would solve my following topic:

http://community.qlik.com/forums/p/25197/96182.aspx#96182

I'd really like you to reply... 🙂