Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rpsrathete
New Contributor

Aggr Function - Help Needed

Hello,

I am new to Qlik Sense.  

I am trying to use aggr function but I am not getting the correct result.  

count(distinct( 

if(  Aggr(Sum(TotalRevenue_INV),AcctExecName,Year_Month_Policy_Eff_and_Acct_Eff_Merged)>=2000 and Is_Contributing_AE=1 and Policy_Eff_and_Acct_Eff_Merged >= AddMonths(Monthstart(Today()),-12) and Policy_Eff_and_Acct_Eff_Merged < AddMonths(Monthstart(Today()),0), AcctExecName 

)))  

Using the above statement, I am getting the correct counts of AcctExecName field but when I try to get the revenue for the same AcctExec field using the statement below I am not getting the correct result.  

Can you please help me understand what I am doing wrong?    

Sum( 

if(  Aggr(Sum(TotalRevenue_INV),AcctExecName,Year_Month_Policy_Eff_and_Acct_Eff_Merged)>=2000 and Is_Contributing_AE=1 and Policy_Eff_and_Acct_Eff_Merged >= AddMonths(Monthstart(Today()),-12) and Policy_Eff_and_Acct_Eff_Merged < AddMonths(Monthstart(Today()),0), TotalRevenue_INV 

))  

Thank you in advance.

Tags (1)
1 Solution

Accepted Solutions

Re: Aggr Function - Help Needed

This to get the total

Sum(Aggr(

If((Sum(If(Policy_Eff_and_Acct_Eff_Merged >= AddMonths(Monthstart(Today()),-12) and Policy_Eff_and_Acct_Eff_Merged < AddMonths(Monthstart(Today()),0) and Is_Contributing_AE=1,TotalRevenue_INV))) > 2000, (Sum(If(Policy_Eff_and_Acct_Eff_Merged >= AddMonths(Monthstart(Today()),-12) and Policy_Eff_and_Acct_Eff_Merged < AddMonths(Monthstart(Today()),0) and Is_Contributing_AE=1,TotalRevenue_INV))))

, AcctExecName))

8 Replies

Re: Aggr Function - Help Needed

Expression doesn't seem wrong, would you be able to share a sample to check this out?

rpsrathete
New Contributor

Re: Aggr Function - Help Needed

I will not be able to share a sample but I have attached the screenshot of the result table.

I am trying to sum the revenue for only two AcctExecName (Lenora and Maya) as they satisfy the condition described in the above statements.

As you can see in the table, count of AcctExecName field is flagged correct but when I am trying to sum the revenue I am getting "0".

I hope this helps.

Aggr.PNG

Re: Aggr Function - Help Needed

What is the expression for Total Revenue?

rpsrathete
New Contributor

Re: Aggr Function - Help Needed

(sum(if(Policy_Eff_and_Acct_Eff_Merged >= AddMonths(Monthstart(Today()),-12) and Policy_Eff_and_Acct_Eff_Merged < AddMonths(Monthstart(Today()),0) and Is_Contributing_AE=1,TotalRevenue_INV)))

Re: Aggr Function - Help Needed

May be this

If((Sum(If(Policy_Eff_and_Acct_Eff_Merged >= AddMonths(Monthstart(Today()),-12) and Policy_Eff_and_Acct_Eff_Merged < AddMonths(Monthstart(Today()),0) and Is_Contributing_AE=1,TotalRevenue_INV))) > 2000, (Sum(If(Policy_Eff_and_Acct_Eff_Merged >= AddMonths(Monthstart(Today()),-12) and Policy_Eff_and_Acct_Eff_Merged < AddMonths(Monthstart(Today()),0) and Is_Contributing_AE=1,TotalRevenue_INV))))

Re: Aggr Function - Help Needed

This to get the total

Sum(Aggr(

If((Sum(If(Policy_Eff_and_Acct_Eff_Merged >= AddMonths(Monthstart(Today()),-12) and Policy_Eff_and_Acct_Eff_Merged < AddMonths(Monthstart(Today()),0) and Is_Contributing_AE=1,TotalRevenue_INV))) > 2000, (Sum(If(Policy_Eff_and_Acct_Eff_Merged >= AddMonths(Monthstart(Today()),-12) and Policy_Eff_and_Acct_Eff_Merged < AddMonths(Monthstart(Today()),0) and Is_Contributing_AE=1,TotalRevenue_INV))))

, AcctExecName))

rpsrathete
New Contributor

Re: Aggr Function - Help Needed

Thank you so much.

If possible can you check my statement for counting AcctExecName? I am trying to count only those have more than 2000 in revenue.

Right now I am getting the correct answer but I am not that confident as I am very new to Aggr functions.

Thanks again.

rpsrathete
New Contributor

Re: Aggr Function - Help Needed

As I have Aggr on AcctExecName and Year_Month_Policy_Eff_and_Acct_Eff_Merged for counting AcctExecNames and in the statement that you gave me it is only using AcctExecName for Aggr. I am trying to understand how it works.

Thank you.

Community Browser