AGGR Function with Distinct

Fri, 06 May 2011 15:43:28 GMT

Please....can someone provide the correct syntax for using the AGGR and distinct fucntions?

This is my current formula but I need to have it sum the order total on a distinct order number. sum(aggr(Sum(Order__c.Total_Order_Amount2__c),Product2.Name))

The order number field will be BR_Order__c

Thanx

AGGR Function with Distinct

Fri, 06 May 2011 16:33:37 GMT

What do you need the distinct for?

Using only a simple aggr(sum(Order__c.Total_Order_Amount2__c), BR_Order__c) won't do what you want?

AGGR Function with Distinct

stevedark — Fri, 06 May 2011 22:50:56 GMT

Hi there,

The fact that you have a sum inside and outside your aggr function means that it is not actually doing anything as a result. Do you not get the same results doing Sum(Order__c.Total_Order_Amount2__c) as you do your current expression?

The purpose of aggr is to enable to you to perform an aggregation over a different type of aggregation across a specifield field or fields. For example to sum only the most expensive known prices for each product:

sum(aggr(max(UnitPrice), Product))

To answer your initial question you will need to clarify what you mean by distinct order number. If you want to see the total order amount broken down by Order Number you would just have the order number as the dimension of a table and the simple Sum expression.

If the issue is that the order amount is duplicated on many rows (perhaps for line items) for each order then it is a statement like this you require:

sum(aggr(max(Order__c.Total_Order_Amount2__c),BR_Order__c))

However it would probably simplify things if you could have the order amount on a order header table and a separate line items table if this is the case. Otherwise you will need to ensure you always use an aggr statement whenever you reference that field - and this will hurt performance slightly.

Hope that helps,

Steve

topic AGGR Function with Distinct in QlikView

AGGR Function with Distinct

AGGR Function with Distinct

AGGR Function with Distinct