Sum - Greater than 0 and Less than 0

(1) Share

Report

Posted on by Prem4253

487

Hello,

I'm using below flow, which filters Current Month and displays the overall sum from Spend Amount column

In Get Items filter query below expression is used:

(CompletionDate ge '@{utcNow('yyyy-MM-01T00:00:00')}') and (CompletionDate lt '@{addToTime(utcNow('yyyy-MM-01T00:00:00'), 1, 'Month', 'yyyy-MM-ddT00:00:00')}')

For a Post message in Teams, I've used below expression for MTD Count:

length(body('Select'))

And below expression for MTD Greater than 0 Spend

formatnumber(xpath(xml(outputs('JSON')), 'sum(/root/Numbers)'), 'C2')

The flow that I have, gives me overall spend, whereas I'm looking to have 2 different rows i.e. one for Greater than 0 and one for less than 0 (in below format):

But the MTD count should show overall count of current month. For e.g. in above screen shot:

Total Count = 47

Greater than 0 = $720,999.44

Less than 0 = ($567,45.22)

Categories:

Business Process Flows

All responses (12)

Answers (0)

Chriddle 7,907 Super User 2025 Season 1 on at

Like (0)

Report

Sum - Greater than 0 and Less than 0

Great catch, Tomac!
Tomac 278 Super User 2025 Season 1 on at

Like (1)

Report
Sum - Greater than 0 and Less than 0

In one of your expressions, you're using a capital letter i instead of a lower case letter L:

xpath(xml(addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select')))), 'sum(//Item/I0)')

Should be:

xpath(xml(addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select')))), 'sum(//Item/l0)')

For the less than 0 figures.

If this fixes the last remaining issue, please mark Chriddle's post as the answer as he provided these expressions.
Chriddle 7,907 Super User 2025 Season 1 on at

Like (0)

Report
Sum - Greater than 0 and Less than 0

concat( formatNumber( div( 720999.4413000001, 1000000 ), 'C2', 'en-us' ), 'M' )
Prem4253 487 on at

Like (0)

Report
Sum - Greater than 0 and Less than 0

@Chriddle

Yes, it was giving incorrect output in Compose.

Later, I recreated both the steps provided by you and it is working now.

I modified below code to covert the currency value into millions:

formatNumber( div ( xpath( xml( addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item', body('GreaternLess') ) ) ), 'sum(//Item/g0)' ), 1000000), 'C2')

Values returning as below;

$0.72

($0.03)

Can you please suggest, how can we add M at the end of the above 2 values. Because we want users to read it as 0.72 million
Chriddle 7,907 Super User 2025 Season 1 on at

Like (0)

Report

Sum - Greater than 0 and Less than 0

I don't get it 😉

... is giving the correct output in Compose Action and In "Post message in a chat or channel"

Now were the value is less than zero, it is displaying 0

There is the correct output in a Compose, but it doesn't work?

This requires further explanation.
Prem4253 487 on at

Like (0)

Report

Sum - Greater than 0 and Less than 0

@Chriddle

All of a sudden, flow has started acting strange. It was / is giving the correct output in Compose Action and In "Post message in a chat or channel"

Now were the value is less than zero, it is displaying 0
Chriddle 7,907 Super User 2025 Season 1 on at

Like (0)

Report
Sum - Greater than 0 and Less than 0

formatNumber( div( 720999.4413000001, 1000000 ), 'C2', 'en-us' )
Prem4253 487 on at

Like (0)

Report
Sum - Greater than 0 and Less than 0

@Chriddle

I figured out a small error in my expressions set in Compose Action. Select2 had a space i.e. Select 2, than I changed this as below:

xpath(xml(addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select2')))), 'sum(//Item/g0)')

And In "Post message in a chat or channel" I again added the expression as below, same as in Compose Action that is added

xpath(xml(addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select')))), 'sum(//Item/g0)') xpath(xml(addProperty( json('{}'), 'Root', addProperty( json('{}'), 'Item',body('Select')))), 'sum(//Item/I0)')

And below is the correct output.

Not sure if I followed the right approach on adding the same expressions both in Compose as well Post message in a chat

Now can you please advise, on how do we convert that values to show in $ Currency with 2 decimals. Basically dividing it by million values, to get the output as below:

$ 0.72

$ (0.03)
Prem4253 487 on at

Like (0)

Report

Sum - Greater than 0 and Less than 0

@Chriddle @Tomac

Below is the flow that is been created but I'm not able to get the desired output (see attached excel for your reference)

field_13 is my Spend Amount column and field_7 is my Completion Date.

The output should be as below in the Teams chat bot:

Total Completion Date Count: 69

Greater than 0 720999.44

Less than 0 -32400.99

Could you please advise further if any additional step or missing step I have missed.

Greater and Less ...
Chriddle 7,907 Super User 2025 Season 1 on at

Like (0)

Report
Sum - Greater than 0 and Less than 0

Sorry, the expressions in my Map l0 and g0 are a bit too much ;)

Instead of this if-equals nonsense, simply use the following:

Map l0:

min(item(), 0)

Map g0:

max(item(), 0)