Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?
7 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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
Champion
Champion

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

Sunil Chauhan
swuehl
MVP
MVP

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

Not applicable
Author

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;

  

AccountProduct1Product2
Client110
Client2200
Client310050
Client400
Client5030
Client6400
Client700
Client8200
Client93030
Client1000
Client11100

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
MVP
MVP

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)

Not applicable
Author

Got it, wonderful. Your first solution worked nicely.

Many thanks Stefan