Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Accumulation with a set analysis in an expression

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.

1 Solution

Accepted Solutions
Not applicable
Author

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


View solution in original post

2 Replies
Not applicable
Author

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?

Not applicable
Author

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