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: 
Anonymous
Not applicable

If statement HELP

HI All,

I'm working on a IF statement where only min Quarter Date and Flow Type = {'Accrued Interest', 'EMV'} will multiple by -1.

Below is what I have so far. Please help. Many thanks.

=If(min(Quarter_Date),Sum({<FlowType={'EMV', 'Accrued Interest'}>}CashFlow)*-1,Sum(CashFlow))

   

   

ABC12/31/2014-12000.00cost
ABC12/31/2014-8000.00cost
ABC12/31/20141600.00cost
ABC12/31/20142400.00cost
ABC12/31/20142815.00Accrued Interest
ABC12/31/20144500.00Accrued Interest
ABC12/31/2014784000.00EMV
ABC12/31/2014117600.00EMV
ABC3/31/2015-120000.00cost
ABC3/31/2015-8000.00cost
ABC3/31/201516000.00cost
ABC3/31/2015240000.00cost
ABC3/31/2015157500.00proceeds
ABC3/31/2015105000.00proceeds
ABC3/31/201565333.33proceeds
ABC3/31/201598000.00proceeds
ABC3/31/201565333.33Accrued Interest
ABC3/31/2015109666.67Accrued Interest
ABC3/31/201519600.00EMV
ABC6/30/2015-12000.00cost
ABC6/30/2015-8000.00cost

Final result should be:

   

ABC12/31/2014-2815.00Accrued Interest
ABC12/31/2014-4500.00Accrued Interest
ABC12/31/2014-784000.00EMV
ABC12/31/2014-117600.00EMV

Any input will be greatly appreciated!.

Frank

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like

=Sum(

If(Quarter_Date = min(TOTAL Quarter_Date) and Match(FlowType,'EMV', 'Accrued Interest'), CashFlow*-1, CashFlow)

)

View solution in original post

5 Replies
sunny_talwar

Where exactly are you trying to do this? Do you have a sample you can share?

swuehl
MVP
MVP

=If(min(Quarter_Date), ...


This doesn't make much sense, because probably it will return TRUE for all or most of the chart lines (since you don't tell what your dimensions and all of your expressions exactely are and without knowing your data, it's hard to precisely tell).


Please post some sample records or a small sample QVW.

Anonymous
Not applicable
Author

Sunny/Swuehl,

Please see attached QVW.

What I'm trying to do here is to have an expression that return the cash flow amount multiply by -1 ONLY where Quarter Date is the min/oldest (12/31/2014 is the oldest date) and Flow Type ='EMV', and 'Accrued Interest". The rest of the cash flow amount remain the same in this "New Cash Flow' column.

Many Thanks!

swuehl
MVP
MVP

Maybe like

=Sum(

If(Quarter_Date = min(TOTAL Quarter_Date) and Match(FlowType,'EMV', 'Accrued Interest'), CashFlow*-1, CashFlow)

)

Anonymous
Not applicable
Author

Thanks Swuehl! looking good so far..