topic Re: If statement HELP in QlikView

If statement HELP

Anonymous — Thu, 04 Feb 2016 20:26:10 GMT

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

Re: If statement HELP

sunny_talwar — Thu, 04 Feb 2016 21:42:07 GMT

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

Re: If statement HELP

swuehl — Thu, 04 Feb 2016 21:43:26 GMT

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

Re: If statement HELP

Anonymous — Thu, 04 Feb 2016 22:10:53 GMT

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!

Re: If statement HELP

swuehl — Thu, 04 Feb 2016 22:30:39 GMT

Maybe like

=Sum(

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

)

Re: If statement HELP

Anonymous — Thu, 04 Feb 2016 22:54:58 GMT

Thanks Swuehl! looking good so far..