Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
SujanJoeJacob
Contributor II
Contributor II

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

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
frontend.jpg

 

 

 

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. 

Labels (1)
5 Replies
saran7de
Master
Master

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

SujanJoeJacob
Contributor II
Contributor II
Author

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]))

Thanks & Regards,
Mayil Vahanan R
SujanJoeJacob
Contributor II
Contributor II
Author

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]))

Thanks & Regards,
Mayil Vahanan R