Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Shir63
Contributor III
Contributor III

Sum if is null divided by Count

Hi everyone,

I want to write a code at the front where if is null "Revenue Full path"

then sum X * Y

else 

Sum(REVENUE_FULL_PATH)

/

count(distinct total <[Opportunity Number]> [Opportunity Item Product Code Adjusted]))

 

but it gives me a total different than the one i see when exporting to excel.

is my formula wrong?

 

if(isnull(REVENUE_FULL_PATH), sum([Opportunity Total Price] * "Opportunity Conversion Rate Today($)"),

Sum(REVENUE_FULL_PATH)
/
count(distinct total <[Opportunity Number]> [Opportunity Item Product Code Adjusted]))

thank you!

 

Labels (1)
1 Reply
MayilVahanan

HI @Shir63 

If its straight table,  use below option change from Auto to Sum.

MayilVahanan_0-1613019643322.png

If its pivot table, then use aggr() like below

Sum(Aggr(if(isnull(REVENUE_FULL_PATH), sum([Opportunity Total Price] * "Opportunity Conversion Rate Today($)"),

Sum(REVENUE_FULL_PATH)
/
count(distinct total <[Opportunity Number]> [Opportunity Item Product Code Adjusted]))

, yourchartdimension))

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.