How can I subtract two results from stats?

[guimilare]

Hi all.

I'm having a hard time trying to make a subtraction..

This is my entry csv:

Date,category,amount,person
01/08/2015,debit,150.00,jose
01/08/2015,debit,130.00,mary
07/08/2015,credit,300.00,jose

What I have so far is:

index=<my_index> | stats sum(amount) as Result by category | addcoltotals labelfield=category label=Total

category                                              Result
debit                                                 280.00
credit                                                300.00
Total                                                 580.00

However, what I want is the difference between Credit and Debit, something like this:

category                                              Result
debit                                                 280.00
credit                                                300.00
Total                                                 20.00

Any ideas how I should write my search?

Thank in advance.

[kdoonan · Accepted solution]

Hi Guimilare,

You could try multiplying one part by -1

index=someindex | eval amount=IF(category=="debit", -1 * amount, amount) | stats sum(amount) as Result by category | addcoltotals labelfield=category label=Total

[guimilare]

I don't get any errors.. But I get the same result as before...

[guimilare]

Hi kdoonan, it worked now!
The thing was that -1 shold come after the field amount:

index=someindex | eval amount=IF(category=="debit", amount*-1, amount) | stats sum(amount) as Result by category | addcoltotals labelfield=category label=Total

Thank you!!

[guimilare]

Hi kdoonan,

It didn't work.
The IF statment is not working... I've tried to change to a positive number (e.g., 10), and no luck either..

Thank you

[kdoonan]

What error do you get when you try to run it and do you have it in the same part of the search?