Aggregate by two fields.

[Fquiroga95]

Hi everyone,

I have a table in front end that looks like this one: 

What I want to do is to have the average of Sum(SalesRevenue*Currency) by week and segment, only for vendors <> "Others".

I tried many things..

So I achieved to have the total of SalesRevenue by week and segment in a column, for each row. Using:

AGGR(NODISTINCT

Sum(SalesRevenue*Currency),
[YYYY-WWW],Segment)

I could perfectly put in set analysis that {<Vendor - ={'Others'}>}, to exclude them.. but first I wanted to ensure that the basic calculation was working.

After that I wanted to have in another column, the total number of rows for that table. I used count(segment), count(Vendor), etc and it wasn't giving me the numbers I was seeing in the table.

I created a Flag that exclude the Vendors="Others". And when I use the same logic I used for calculating the revenue by week and segment:

AGGR(NODISTINCT

SUM( FLAG_VENDOR_NO_OTHERS),
[YYYY-WWW],Segment)

Is giving me a different number = 20, instead if the 7 rows I am seeing in the table. I also tried to do a simple SUM ( FLAG_VENDOR_NO_OTHERS) and per row is giving me more than one result. 

I need a column with 7, which is the number of rows without Others in Vendor, that exist in the table I have in front end.

Where that number 20 comes from? Well, the thing is that is doing the operation with all the data from the original table. In the original table I have more fields than in the table I am using in the front end to do the calculations. So indeed there are 20 rows in the original table in the backend, associated with Vendor different than others, for week "2022-W02" and Segment "Small Business". 

But when I put the specific combination of Week, Distributor, Country, Segment and Vendor there are only 7, and that's the number I need to achieve with a calculation. Because I will then divide the total revenue by that number for each row. 

So putting this: 

AGGR(NODISTINCT

SUM( FLAG_VENDOR_NO_OTHERS),
[YYYY-WWW],Segment)

In my front end table is not working, because it use all the backend data.

Any idea what I should do ?

Hopefully the explanation was clear.

Kind regards.

[rubenmarin]

Hi, I'm not sure of the final result you are searching... have you tried this?:

Sum(TOTAL <Week,Segment> {<Vendor-={'Others'}>} SalesRevenue*Currency)