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

Sum if expression

hi All

Could you please confirm whether the below given expression is accurate

sum(If(Sales='0',Planned,Sales))

Month     Sales,   Planned

Jun           100       100

Jul             150         100

Aug             0            200

Based on above sample data, i would like to calculate  cumulative sales  for  3 months

Sum  all sales but if sales is equal to zero , then planned value should be aggregated

Thus , total value for three months should 100+150+200 = 450

BR

Jayanthan

1 Solution

Accepted Solutions
MarcoWedel

Hi,

I guess you're looking for IsNull(Sales) rather than Sales=0:

QlikCommunity_Thread_131352_Pic1.JPG.jpg

hope this helps

regards

Marco

View solution in original post

11 Replies
ecolomer
Master II
Master II

I think the easiest thing would be to create a new field that contains or Planned Sales, Sales according to either = 0 or not.

Then this calculated field new

israrkhan
Specialist II
Specialist II

try like

if(sum(Sales=0), sum(Planned), sum(Sales))

mdmukramali
Specialist III
Specialist III

Dear ,

Sales:

load * Inline

[

Month,Sales,Planned

Jun,100,100

Jul,150,100

Aug,0,200

];

LOAD * ,

if(Sales=0,Planned,Sales) as Sales_planned

Resident Sales;

DROP Table Sales;

Thanks.

MarcoWedel

sum(If(Sales=0,Planned,Sales))


Should work.


Regards

Marco

Not applicable
Author

hi all

it doesn not work

BR

Jayanthan

MarcoWedel

Please post sample APP/data

Not applicable
Author

Hi Macro

Please find QVW and data source

Jayanthan

ashfaq_haseeb
Champion III
Champion III

Hi,

It works as expected.

What are you trying to achieve here.

Can you post your required output .

Regards

ASHFAQ

MarcoWedel

Hi,

I guess you're looking for IsNull(Sales) rather than Sales=0:

QlikCommunity_Thread_131352_Pic1.JPG.jpg

hope this helps

regards

Marco