Need help to make aggr() expression work with Valuelist using island table

[SujanJoeJacob]

Hi all,
I'm trying to use aggr() expression in valuelist(). 
I know aggr() doesn’t seem to work in combination with ValueList() due to  “grain mismatch”.  And need to use island table. 

But facing difficulties in creating one and understand how island table works. 

Front End Table

Dimension:
KPI_Dimension:
Load * INLINE [
KPI_Metric
GMV
Discounts
Cancellation
Return
NetSales
];


Expression:

Pick(Match(ValueList('GMV', 'Discounts', 'Cancellation','Return','Net Sales'),
'GMV', 'Discounts', 'Cancellation','Return','Net Sales'),

Sum(Aggr(Only(line_items_price), [order_id])),

Sum(Aggr(Only(total_discounts), [order_id])),

Sum(Aggr(Only({<fulfilment_flag = {"unfulfilled"}, cancelled_flag = {"Yes"}>} line_items_price ), [order_id])),

Sum(Aggr(Only({<fulfillment_status = {"fulfilled"}, cancelled_flag = {"Yes"}>} line_items_price ), [order_id]))

......

)

Need help on the approach to create island table. 

[Saravanan_Desingh]

I think, if you use Aggr along with Pick & Match, it will give wrong results. May be try avoiding it.

[SujanJoeJacob]

What's the alternative for  > Sum(Aggr(Only(line_items_price), [order_id]))? 
To get a sum grouped on dimension. 

[MayilVahanan]

Hi Sujan

Sum(Aggr(Only(line_items_price), [order_id]))

Based on my understanding, one order has many order item & each item has their own item price. If its the case, you need to use sum(line_items_price) instead of only(). can you try like  below?

Sum(Aggr(Sum(line_items_price), [order_id]))

[SujanJoeJacob]

Hey Mayil ,
Thanks for your reply. 

Yes, that's problem with our schema. 
Unfortunately, fields like discounts, subtotal are stored on order level and not line item level. 

Hence have to do grouping and aggr() on order_id. 
Any alternative to group without aggr()?

[MayilVahanan]

Hi,

For Discounts & SubTotal, you can use Sum() function alone without Aggr.

For line_items_price, if you want to use aggr by Order Id based on ur requirement, try like below

Sum(Aggr(Sum(line_items_price), [order_id]))