Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

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

Not applicable

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.


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
];

chauhans85
Esteemed Contributor

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

MVP
MVP

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

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

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;

  

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

MVP
MVP

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)

Not applicable

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

Got it, wonderful. Your first solution worked nicely.

Many thanks Stefan

Community Browser