Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
ABC | 12/31/2014 | -12000.00 | cost |
ABC | 12/31/2014 | -8000.00 | cost |
ABC | 12/31/2014 | 1600.00 | cost |
ABC | 12/31/2014 | 2400.00 | cost |
ABC | 12/31/2014 | 2815.00 | Accrued Interest |
ABC | 12/31/2014 | 4500.00 | Accrued Interest |
ABC | 12/31/2014 | 784000.00 | EMV |
ABC | 12/31/2014 | 117600.00 | EMV |
ABC | 3/31/2015 | -120000.00 | cost |
ABC | 3/31/2015 | -8000.00 | cost |
ABC | 3/31/2015 | 16000.00 | cost |
ABC | 3/31/2015 | 240000.00 | cost |
ABC | 3/31/2015 | 157500.00 | proceeds |
ABC | 3/31/2015 | 105000.00 | proceeds |
ABC | 3/31/2015 | 65333.33 | proceeds |
ABC | 3/31/2015 | 98000.00 | proceeds |
ABC | 3/31/2015 | 65333.33 | Accrued Interest |
ABC | 3/31/2015 | 109666.67 | Accrued Interest |
ABC | 3/31/2015 | 19600.00 | EMV |
ABC | 6/30/2015 | -12000.00 | cost |
ABC | 6/30/2015 | -8000.00 | cost |
Final result should be:
ABC | 12/31/2014 | -2815.00 | Accrued Interest |
ABC | 12/31/2014 | -4500.00 | Accrued Interest |
ABC | 12/31/2014 | -784000.00 | EMV |
ABC | 12/31/2014 | -117600.00 | EMV |
Any input will be greatly appreciated!.
Frank
Maybe like
=Sum(
If(Quarter_Date = min(TOTAL Quarter_Date) and Match(FlowType,'EMV', 'Accrued Interest'), CashFlow*-1, CashFlow)
)
Where exactly are you trying to do this? Do you have a sample you can share?
=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.
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!
Maybe like
=Sum(
If(Quarter_Date = min(TOTAL Quarter_Date) and Match(FlowType,'EMV', 'Accrued Interest'), CashFlow*-1, CashFlow)
)
Thanks Swuehl! looking good so far..