Incorrect totals in a pivot table using set analysis

[#1]

Hi everyone,

I have a small issue trying to add the sumtotals correctly in a pivot table.  My set analysis for the table currently has an IF statement, and selecting the date range for the calendar consists of two variables (vStartDate and vEndDate).

The IF statement determines if the number of rows submitted equals the number of months between the start and end dates.  My dilemma is calculating the totals.  When a row has a NULL in an expression because of an invalid time range, it may still have a value in the database.  This value is being used to calculate the total, even though it is displayed as a NULL on the table.

How can I incorporate my IF condition (comparing count of rows to the number of months between the start and end dates) into the second line of my expression?  So it would serve as a filter for my SUM statement.

________________________________________________

=IF(Count( {$<[Finance Date] = {'>=$(=MonthStart(Date(vStartDate)))<=$(=Date(vEndDate))'} >} [Finance Performance ID]) >= $(vDateDiff),

SUM( {$<[Finance Date] = {'>=$(=MonthStart(Date(vStartDate)))<=$(=Date(vEndDate))'} >} [Total Revenue]),NULL)

WHERE vDateDiff is a variable defined as:  round(($(=vStartDate)-$(=vEndDate)) / 30.25)

[johnw · Accepted solution]

The general way to get a sum of rows in a pivot table is like this:

sum(aggr(YourExpressionHere,YourDimensionsHere))

The set expression would need to be repeated on the new sum(), so I believe you'd need this:

sum({$<[Finance Date] = {'>=$(=MonthStart(Date(vStartDate)))<=$(=Date(vEndDate))'} >}
aggr(IF(Count( {$<[Finance Date] = {'>=$(=MonthStart(Date(vStartDate)))<=$(=Date(vEndDate))'} >} [Finance Performance ID]) >= $(vDateDiff),
SUM( {$<[Finance Date] = {'>=$(=MonthStart(Date(vStartDate)))<=$(=Date(vEndDate))'} >} [Total Revenue]),NULL)
,YourDimensionsHere))

And of course replace "YourDimensionsHere" with your actual chart dimensions, comma delimited.  You may want to use a variable for your set expression to avoid repeating it this many times.