topic Re: Accumulation with a set analysis in an expression in QlikView

Accumulation with a set analysis in an expression

Sat, 14 Mar 2015 10:37:29 GMT

Hopefully I can explain this correctly.

I have a set analysis I'm trying to use to calculate a percentage per month (I use a variable so that my expression isn't five miles long).

If I use the expression count($(setAnalysisVariable) [Field to be counted]), and then set full accumulation for the expression I get a proper chart:

However, when I combine this with my full equation (round((373/(count($(setAnalysisVariable) [Field to be counted])+ 373)),.0001)*100), accumulation accumulates the percentage total:

How do I fix this equation so it accumulates the count in the expression and calculates the chart properly (the Mar-15 entry should be 73.14%, not 1464.46%, the Dec-13 entry is correct).

I should add that I'm using a calculated dimension that resizes my calendar based on how many items are present (more than 16 months converts the calendar to years). I'm assuming this is why my attempt to use "Aggr" does not work.

Re: Accumulation with a set analysis in an expression

Mon, 16 Mar 2015 15:30:03 GMT

Ok, update on this, I've been experimenting with the above statement with some results.

My current expression is this:

Above(count($(SetAnalysisVar) [Field Name]), 1,2)

From the docs, above should follow above([expression], [offset], ), where offset is the number of rows above the current one to use, and where n tells the above statement to evaluate a range of above's.

First, I can't seem to get the n part of the expression to work. No matter what number I put in that field, no changes appear in the chart.

I was thinking I could create a statement like so:

Above(count($(SetAnalysisVar) [Field Name]), RowNo()-1, RowNo())

The idea being that each successive row would essentially accumulate the counts from all prior rows, however this particular expression literally produces nothing.

Anyone have ideas on what I am doing wrong?

Re: Accumulation with a set analysis in an expression

Tue, 17 Mar 2015 20:46:40 GMT

Ok, so I finally solved my issue, turns out I was on the right track, just the wrong function. I needed to Sum a range of counts (rangesum).

My amended expression now looks like this:

round(373/

(373+ Rangesum(Above(count($(SetAnalysisVar) [Field To Count]), 1, RowNo()-1))+ count($(SetAnalysisVar2) [Field To Count])),.0001)*100

So my chart % now appears correctly (starts at 100% and goes down over time):