sum if?

[Anonymous]

Hey. im trying the following but it returns null.

if(

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

or

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

[#2]

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

      0)

)

[#3]

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)

         )

or

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.

[#4]

this works

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

Sum (

         { <   CHARGETYPECODE = {'SALES'},

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

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

             > }

          TOTAL

         )

[Peter_Cammaert]

What about the additional set modifier in the debit sum?

[#6 · Accepted solution]

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

         )

[Anonymous]

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

this worked.

thanks very much to all.