Can Sum Value be use in a different coulumn

Aneesha_Gattamaneni · November 10, 2021, 2:38am

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

cf1
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

cf2
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

seinchyi · November 10, 2021, 8:22am

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: