Aggr Function - Help Needed

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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.

[sunny_talwar]

What is the expression for Total Revenue?

[Anonymous]

(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)))

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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

[Anonymous]

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.

[Anonymous]

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.