Set Expression Only Working If Filered in Pivot Table

[andrew58972]

I am trying to show the cumulative totals in a pivot table for the total amount of visits. 

The expression that I am currently using is this: =if(Dimensionality()=3,if(sum(dwell_time)=0,0,count(distinct {<spot_number={"=Sum(dwell_time)>0"}>} Visits)),
sum({<Visits>} TOTAL <building_name, spot_group> aggr(count(distinct {<spot_number={"=Sum(dwell_time)>0"}>} Visits), building_name, spot_group, spot_number)))

I only want to count the distinct visits if the dwell_time, which is a variable that is not being used in the pivot table, is greater than 0. However, the calculation is only correct when there is a filter and when there is no filter, the Visits is not correct. As seen the Total Visits is different for the same data set with a filter. 

 

 

[TauseefKhan]

Hi @andrew58972,
Check with this one: 

IF(Dimensionality() = 3,
IF(Sum(dwell_time) = 0,
0,
Rangesum(Above(
Count(DISTINCT {<spot_number={"=Sum(dwell_time) > 0"}>} Visits),
0,
RowNo(TOTAL)
))
),
Sum({<Visits>} TOTAL <building_name, spot_group>
Rangesum(Above(
Count(DISTINCT {<spot_number={"=Sum(dwell_time) > 0"}>} Visits),
0,
RowNo(TOTAL)
))
)
)

Note: If RowNo(TOTAL) is not worked check NoOfrows(TOTAL)

 *** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. ***

[andrew58972]

Thanks for the reply,

However while doing this I am getting an error because nested aggregation is not allowed.