Cumulative percentage with Rangesum problem with set analysis

[wayne-a]

Hi, I'm having some trouble with a rangesum that calculates a cumulative percentage and trying to apply set analysis.  This is used in a chart to show a percent that met a criteria against the total set.  The data was being filtered in the load script to just take the last 7 days and worked as expected in this case.  Request was made for an additional view over 6 months so I changed the load script to bring in 6 months of data and added a field that gets set as an indicator if the data is within the last 7 days thinking I just needed to add some simple set analysis to the original chart.  When I did this, it only calculates the values for the last 7 days correctly but I get an entry for every date, not jus the ones that meet the set analysis criteria.  I don't want all the extra dates that show zeros.

In this image the chart shows the dates with no values, highlighted in yellow.

This is the rangesum function with the set analysis

Rangesum(above(Count({$<Last7Days={'Y'}>}[Order#])/ Aggr(nodistinct Count({$<Last7Days={'Y'}>}[Order#]),[Date Received]),0,RowNo()))

I've changed the set analysis condition to look at the [Date Received] field specifically and get the same results.  If I remove the set analysis condition from the Aggr I get the same results so it's like it doesn't even apply the set analysis there.  Thanks in advance for any help.

[jwjackso · Accepted solution]

Have you tried un-setting the "Data handling"->"Include zero values" or in the dimension use an expression and suppress Nulls

If (Last7Days='Y',[Date Received],Null()) 

[wayne-a]

Jwjackso thank you very much!  Setting the Data handling to not include zero values did it.  I completely missed that option.