topic Re: RangeSum in Pivot Table in App Development

RangeSum in Pivot Table

Roberto03 — Wed, 11 Jan 2023 11:19:53 GMT

Hi all,

I have created a pivot table with 2 dimensions 'Denominazione' and 'Month(Data_Calendario)'

The measure (GG_ Franchigia_KPI_A) is calulated as follow:

SOGLIA_TEMP_KPI_A - SUM(CABINA_INATTIVA)*PERC_A) - rangesum(above(total sum(ANOMALIE_KPI_A_SNAM), 0, rowNo()))

where the value "SOGLIA_TEMP_KPI_A - SUM(CABINA_INATTIVA)*PERC_A) "

is progressively reduced on the basis of "rangesum(above(total sum(KPI_A_SNAM) ANOMALIES), 0, rowNo()) " values.

however I can't get the maximum value (-26.25) to appear in the pivot totals row at the top

I have used

Max(TOTAL Aggr(

(SOGLIA_TEMP_KPI_A - SUM(CABINA_INATTIVA)*PERC_A) - rangesum(above(total sum(ANOMALIE_KPI_A_SNAM), 0, rowNo()))

, Denominazione))

min( total <Denominazione,DATA_CALENDARIO> Aggr(

(SOGLIA_TEMP_KPI_A - SUM(CABINA_INATTIVA)*PERC_A) - rangesum(above(total sum(ANOMALIE_KPI_A_SNAM), 0, rowNo()))

, Denominazione, (DATA_CALENDARIO, (Ascending))))

but i only have the max value in all cells

Thx in advance

Re: RangeSum in Pivot Table

E_Røse — Wed, 11 Jan 2023 14:09:08 GMT

@Roberto03

Can use the dimensionality() function, to choose from two different expressions?

Re: RangeSum in Pivot Table

Roberto03 — Wed, 11 Jan 2023 14:14:40 GMT

THANKS @E_Røse

how it work, from the pics I can not see the formula.

thanks

Re: RangeSum in Pivot Table

Roberto03 — Wed, 11 Jan 2023 14:28:30 GMT

ok I have found

if(dimensionality() = 1,
min( total <Denominazione,DATA_CALENDARIO> Aggr(
(SOGLIA_TEMP_KPI_A - SUM(CABINA_INATTIVA)*PERC_A) - rangesum(above(total sum(ANOMALIE_KPI_A_SNAM), 0, rowNo()))
, Denominazione, (DATA_CALENDARIO, (Ascending))))
,if( dimensionality()= 2,
(SOGLIA_TEMP_KPI_A - SUM(CABINA_INATTIVA)*PERC_A) - rangesum(above(total sum(ANOMALIE_KPI_A_SNAM), 0, rowNo())))
)

thanks