Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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. ***
Thanks for the reply,
However while doing this I am getting an error because nested aggregation is not allowed.