Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
)
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)
)
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.
this works
IF (DC='DR',-1,1) *
Sum (
{ < CHARGETYPECODE = {'SALES'},
CalendarMonth={'=$(vMaxMonthYear)'},
[FiscalYear]={"=$(vMaxYear)"}
> }
TOTAL
)
What about the additional set modifier in the debit sum?
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
)
(Sum({<CHARGETYPECODE={"SALES"},CalendarMonth={'$(vMaxMonthYear)'},[FiscalYear]={"$(vMaxYear)"},[FiscalMonth]=>}INVOICETOTAL))*(if((DC='DR'), -1,1))
this worked.
thanks very much to all.