topic Re: Sum if expression in QlikView

Sum if expression

Sun, 31 Aug 2014 08:15:30 GMT

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

Jayanthan

Re: Sum if expression

ecolomer — Sun, 31 Aug 2014 08:40:42 GMT

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

Re: Sum if expression

israrkhan — Sun, 31 Aug 2014 08:42:23 GMT

try like

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

Re: Sum if expression

mdmukramali — Sun, 31 Aug 2014 08:45:11 GMT

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.

Re: Sum if expression

MarcoWedel — Sun, 31 Aug 2014 10:25:13 GMT

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

Should work.

Regards

Marco

Re: Sum if expression

Sun, 31 Aug 2014 10:52:36 GMT

hi all

it doesn not work

Jayanthan

Re: Sum if expression

MarcoWedel — Sun, 31 Aug 2014 11:15:11 GMT

Please post sample APP/data

Re: Sum if expression

Sun, 31 Aug 2014 11:27:09 GMT

Hi Macro

Please find QVW and data source

Jayanthan

Re: Sum if expression

ashfaq_haseeb — Sun, 31 Aug 2014 11:35:16 GMT

Hi,

It works as expected.

What are you trying to achieve here.

Can you post your required output .

Regards

ASHFAQ

Re: Sum if expression

MarcoWedel — Sun, 31 Aug 2014 11:51:41 GMT

Hi,

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

hope this helps

regards

Marco

Re: Sum if expression

Sun, 31 Aug 2014 12:03:43 GMT

Hi Macro

Yeah, i was looking IsNull .Thank You

Jayanthan

Re: Sum if expression

MarcoWedel — Sun, 31 Aug 2014 12:14:00 GMT

you're welcome

regards

Marco