7 Replies Latest reply: Feb 10, 2017 3:30 PM by Marcelo Mtanous

# Multiple conditions in aggr(sum(if(x AND y)... help

Hi, need some help - someone up a saturday?
I want to add a condition to the following existing calc:
aggr(sum(qty_a),label) / aggr(sum(qty_b),label)

I only want to sum if the rows match my variable that is:
CurrentMonth = =num(month(Today()))
Formula something like:
=aggr(sum(if(iMonth=Currentmonth,qty_a,0),label) / aggr(sum(if(iMonth=CurrentMonth,qty_b,0),label)
*Where iMonth is the month-number from the data rows.

Data sample:
iMonth, Label, qty_a, qty_b
1, ALFA, 5, 10
2, ALFA, 0, 5

Any ideas?
• ###### Multiple conditions in aggr(sum(if(x AND y)... help

Hi,

not sure if I understood correctly...

You are using a (table) chart, with dimension iMonth, correct?

Maybe you want to try this (I just added iMonth to your aggr-dimensions and one missing bracket):

=aggr(sum(if(iMonth=CurrentMonth,qty_a,0)),Label,iMonth)

/ aggr(sum(if(iMonth=CurrentMonth,qty_b,0)),Label,iMonth)

Regards,

Stefan

• ###### Multiple conditions in aggr(sum(if(x AND y)... help

Hi Swuehl,
Using a pivot, and the data is loaded from a database.

The goal is to compare current vs last month to get a trend... (in the end).

Your suggestion didnt work, but I get no errors eithers.

[iMonth, Label, qty_a, qty_b
1, A, 2, 10
1, A, 2, 5
2, A, 1, 20
2, A, 3, 10
];

//Or a variable...

[CurrentMonth
2
];

• ###### Multiple conditions in aggr(sum(if(x AND y)... help

vlastMonth=CurrentMonth-1

aggr(sum(if(iMonth=\$(CurrentMonth),qty_a,0)),Label,iMonth)

/ aggr(sum(if(iMonth=\$(vlastMonth),qty_b,0)),Label,iMonth)

hope this help

• ###### Re: Multiple conditions in aggr(sum(if(x AND y)... help

I put my exact above expression in a pivot and it seems to work (I attached my sample, please have a look).

(I duplicated your data up to december, because current month is September, so I assume we want to have data for todays month?)

If you want to compare current vs last month in a table with iMonth dimension, you may wanna take a look into the chart inter record function, above() for example.

I created a second table as example for that.

Regards,

Stefan

• ###### Re: Multiple conditions in aggr(sum(if(x AND y)... help

Hi Stefan swuehl

I am struggling with a similar topic. I would like to use two conditions within the aggr function to calculate how many clients buy from the two products. See below my sample table;

 Account Product1 Product2 Client1 1 0 Client2 20 0 Client3 100 50 Client4 0 0 Client5 0 30 Client6 40 0 Client7 0 0 Client8 20 0 Client9 30 30 Client10 0 0 Client11 10 0

The expression I am using for counting the clients that buy from product1 is the below;

Count(distinct if(aggr(sum({\$<[Product Line] ={'Product1'}>} Run_Rate__c), Account) > 0, Account))

How can I add into the aggr function the second product, Product2, so I can get only the clients that buy from both. In this case, 2 will be the answer.

Appreciate very much your help

• ###### Re: Multiple conditions in aggr(sum(if(x AND y)... help

Maybe like

=Count(DISTINCT

Aggr(

If( Sum({<[Product Line] = {'Product1'}>} Run_Rate_c) >0

and Sum({<[Product Line] = {'Product2'}>} Run_Rate_c) >0, Account)

,Account)

)

Or

=Count(DISTINCT

{<Account = {"=Sum({<[Product Line] = {'Product1'}>} Run_Rate_c) >0

and Sum({<[Product Line] = {'Product2'}>} Run_Rate_c) >0"}

>}

Account)

Maybe it's enough to check if there is a relation between Account and the two Product lines:

=Count(DISTINCT

{<

Account = p({<[Product Line] = {'Product1'}>}) * p( {<[Product Line] = {'Product2'}>})

>}

Account)

• ###### Re: Multiple conditions in aggr(sum(if(x AND y)... help

Got it, wonderful. Your first solution worked nicely.

Many thanks Stefan