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

[#1]

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?

[swuehl]

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

[#3]

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.

LOAD * INLINE
[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...

LOAD * INLINE
[CurrentMonth
2
];

[SunilChauhan]

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

[swuehl]

Hi tobbeandersson,

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

[#6]

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

[swuehl]

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)

[#8]

Got it, wonderful. Your first solution worked nicely.

Many thanks Stefan