Aggregation Function in Chart Table Showing in First Row Only

[Chandan_Nadve]

Dear All,

Here am replicating the sample dataset of my real time project. 

I am using the aggregation sum to take individual Partywise Total Advance Receipt or Recievable .  But Aggregation with is working fine in KPI but not in chart, i found some solution in Community to use "nodistinct", but its not giving expected output.

Please help me to get the following expected output.

Data Table:

PartyGroup	PartyName	Amount
TradeRecievable	A	100
TradeRecievable	B	-300
OtherRecievable	C	400
OtherRecievable	D	800
OtherRecievable	E	-300
TradeRecievable	F	200
TradeRecievable	G	200
TradeRecievable	H	-300
OtherRecievable	I	100
OtherRecievable	D	200
OtherRecievable	E	-300
TradeRecievable	F	200

Expected Output in table chart:

Details	Amount
Total Advance Received	-1200
Total Recievable	2200

Expression used:

Pick(ReportHeadID,Sum(Aggr(nodistinct If(Sum(Amount)<0,Sum(Amount),0),PartyGroup,PartyName)),
Sum(Aggr(nodistinct If(Sum(Amount)>0,Sum(Amount),0),PartyGroup,PartyName)))

DataLoadScript:

SummaryTable:
Load*Inline
[ReportHead,ReportHeadID
Total Advance Recieved,1
Total Recievable,2
];

DetailTable:
Load*Inline
[PartyGroup,PartyName,Amount
TradeRecievable,A,100
TradeRecievable,B,-300
OtherRecievable,C,400
OtherRecievable,D,800
OtherRecievable,E,-300
TradeRecievable,F,200
TradeRecievable,G,200
TradeRecievable,H,-300
OtherRecievable,I,100
OtherRecievable,D,200
OtherRecievable,E,-300
TradeRecievable,F,200
];

Report Format Required

Best Regards

Chandan

[jatin3001]

Hi Chandan,

You can resolve this with a different solution using backend and frontend. PLease find below solution:

SummaryTable:
Load*Inline
[ReportHead,ReportHeadID,Link
Total Advance Recieved,1,-1
Total Recievable,2,1
];

DetailTable:
Load*Inline
[PartyGroup,PartyName,Amount,Link
TradeRecievable,A,100,1
TradeRecievable,B,-300,-1
OtherRecievable,C,400,1
OtherRecievable,D,800,1
OtherRecievable,E,-300,-1
TradeRecievable,F,200,1
TradeRecievable,G,200,1
TradeRecievable,H,-300,-1
OtherRecievable,I,100,1
OtherRecievable,D,200,1
OtherRecievable,E,-300,-1
TradeRecievable,F,200,1
];

Link in Detail table can be captured based on condition if( Amount<0,-1,1)
and in UI you have to pull the reported head as dimension and Sum(Amount) as a measure