Can Sum Value be use in a different coulumn

This is the sample data in my table

Org Team due date assignee
O1 T1 8-Nov abc
O1 T1 8-Nov abc
O1 T1 7-Nov bcd
O1 T2 15-Dec cde
O2 T3 15-Dec cde
O2 T3 7-Nov def
O2 T3 7-Nov def
O2 T3 7-Nov def

I am expecting to display the below in the QS dashboard pivot table

I am trying to calculate the ‘number of team that past the due date’ (Num) and display it as Num-assignee

Org Team Past due
O1 T1 2-abc
T1 1-bcd
O2 T3 3-def

In order to do this, I calculated the due date with the calculated field (cf1) below

ifelse( dateDiff(truncDate(‘DD’, now()),duedate) > 0 , 0, 1)

Now in order to display the Past due column, I tried with the calculated field (cf2) below

ifelse({cf1}>0, concat(toString({cf1}),’-’,{assignee}),’-’) but it does not work, it gives me the output as below. Its not taking the Sum of the calculated field cf1

Just wondering if I can use the sum of the calculated field in anyway ? Or there is another recommended way to achieve this?

Org Team Past due
O1 T1 1-abc
T1 1-bcd
O2 T3 1-def

You will need to first get the total records of past dues by assignee, the concatenate the result to the assignee. Another problem here is that for pivot table, you cannot have custom aggregation field in Rows/Columns field well. Hence, you will need to use Level Aware Aggregation to perform the aggregation, which the result can then be treated as a dimension that you can use in Rows/Columns field well.

The first calculated field is correct. Try the following expressions for your second calculated field: concat(toString(sumOver({days past due},[assignee],PRE_AGG)),"-",assignee)

End-result will look something like this: