topic sum if? in QlikView

sum if?

Anonymous — Thu, 08 Jan 2015 21:22:49 GMT

Hey. im trying the following but it returns null.

if(

-(Sum({<CHARGETYPECODE={"SALES"},DC={"DR"},CalendarMonth={'$(vMaxMonthYear)'},[FiscalYear]={"$(vMaxYear)"},[FiscalMonth]=>}TOTAL))

(Sum({<CHARGETYPECODE={"SALES"},DC={"CR"},CalendarMonth={'$(vMaxMonthYear)'},[FiscalYear]={"$(vMaxYear)"}>}TOTAL)))

how may I achieve the following?

what I'm after is if dc="dr" then -sum(to return negative value) else if dc="cr" sum (return total)

thanks.

Re: sum if?

Thu, 08 Jan 2015 22:55:53 GMT

Hi,

Write exactly like that

if(dc='DR', -1 * SUM({...negative conditions ..} TOTAL),

if( dc='CR', SUM(... positive set))

)

everything else will be null in above. You can make all other conditions to 0 or something else with below

if(dc='DR', -1 * SUM({...negative conditions ..} TOTAL),

if( dc='CR', SUM(... positive set),

// else condition

)

Re: sum if?

Thu, 08 Jan 2015 23:33:10 GMT

Looks like the only variation is dc and the -1 to return a negative value.

consider the following IF, it returns -1 if DC='DR' else it returns 1

IF (DC='DR',-1,1)

given the above

We can use

Sum (

{ < CHARGETYPECODE = {'SALES'},

CalendarMonth={'=$(vMaxMonthYear)'},

[FiscalYear]={"=$(vMaxYear)"}

> }

TOTAL * (IF (DC='DR',-1,1)

)

IF (DC='DR',-1,1) *

Sum (

{ < CHARGETYPECODE = {'SALES'},

CalendarMonth={'=$(vMaxMonthYear)'},

[FiscalYear]={"=$(vMaxYear)"}

> }

TOTAL

)

Please try, I don't have my QlikView desktop at this momento so I can't check the sintax but the logic should be that.

Re: sum if?

Thu, 08 Jan 2015 23:39:09 GMT

this works

IF (DC='DR',-1,1) *

Sum (

{ < CHARGETYPECODE = {'SALES'},

CalendarMonth={'=$(vMaxMonthYear)'},

[FiscalYear]={"=$(vMaxYear)"}

> }

TOTAL

)

Re: sum if?

Peter_Cammaert — Thu, 08 Jan 2015 23:51:35 GMT

What about the additional set modifier in the debit sum?

Re: sum if?

Thu, 08 Jan 2015 23:57:50 GMT

You are right , but les't ask bon if [FiscalMonth]= should exist in both negative and positive values. I think that based on the expression it should exist to remove Fiscal Month Selection.

IF (DC='DR',-1,1) *

Sum (

{ < CHARGETYPECODE = {'SALES'},

[FiscalMonth]= ,

CalendarMonth={'=$(vMaxMonthYear)'},

[FiscalYear]={"=$(vMaxYear)"}

> }

TOTAL

)

Re: sum if?

Anonymous — Fri, 09 Jan 2015 02:22:24 GMT

(Sum({<CHARGETYPECODE={"SALES"},CalendarMonth={'$(vMaxMonthYear)'},[FiscalYear]={"$(vMaxYear)"},[FiscalMonth]=>}INVOICETOTAL))*(if((DC='DR'), -1,1))

this worked.

thanks very much to all.