Server Connect Average Value IF

[fatherofinvention]

Hey everybody.

I am making an API call and I need take an average of one of the fields only if its value is greater than or equal to 0. Programmatically I know precisely how this is done, but I am having a difficult time constructing the action steps.

The JSON looks like this:

I tried using repeat and condition actions, but could not figure it out. I am assuming a repeat action is needed here and I thought the condition action would be needed in order to average the value ONLY if it is greater than or equal to 0.

This is what I have so far:

@Teodor… smart money says you know the answer to this one.

[Teodor]

Hey Mason,
So if I understand you right, you want to show the average of all change fields returned by the repeat region only when their value is >= 0 ?

[fatherofinvention]

Yes sir, that is exactly right!

[Teodor]

Remove the condition step and add the where filter directly to the repeat step expression
Then inside the repeat step add a setvalue step and use the average filter there. Enable output option for the setvalue step, in order for it to be available in the date picker on your page.

[fatherofinvention]

Ahh, ok. The logic is beginning to clarify itself in my mind now. I think I am getting close. I have added the where filter as you have suggested, but when I do this and then Output Fields becomes an empty list:

So when I go to add the set value step inside of the repeat, I don’t get any fields to choose from to apply the average filter to:

By the way, if it helps you to work with the API directly it is here: https://api.iextrading.com/1.0/stock/aapl/chart/1m

No headers or auth are required.

[Teodor]

Ah sorry, I think I misled you
You won’t need the repeat step at all. Just a setvalue step like:

{{api_iex_chart_1m.data.where(change, ">=", 0).avg(change)}}

Not sure if that’s the right syntax as I am on my phone now, away from the computer

[George]

Yes you can achieve all this in a single expression by using the right data formatters.

That is why we have so many ready to go data formatters for filtering, transformation, calculation and formatting.

[fatherofinvention]

Hey @Teodor

I tried using that expression but it produced an error. Then I tried to build it out visually but the Data Formats window will not allow me to press Select to save it.

When using the expression you provided I receive this error:

[fatherofinvention]

Cool, thank you @George. I figured it would be possible, I doubt there are very many things that cannot be done within Wappler. It is a very capable platform.

My question is, how do I build this logic out visually

[Teodor]

I’ll check that later on my computer and will let you know

[fatherofinvention]

Thank you very much @Teodor. It’s bed time here in California now! I have been using Wappler all day. I think it is safe to say that I’m addicted. Take care and talk to you later.

[fatherofinvention]

Sitting down to Wappler first thing with a cup of coffee. The mind is clear today.

So using the input that has been given so far this is what I’ve come up with:

And my output is:

So is this correct? Let’s verify with a little PowerShell:

Indeed! So these steps work perfectly. My only niggle: instead of having two set value steps, one to filter the collection down and then another to average it, I would like to do this in a single step and be able to construct it visually, not just by typing the expression.

Can anyone spot what is wrong with my syntax here? I am trying to divide the sum of one collection by the count of another. I am aware that there is an average function but in this case, it will not work in this particular step of the calculation because it needs to account for properties with 0 values, which do not exist in the lookback_gains collection. Therefore:

{{(lookback_gains.sum("change"))/(lookback_period_adj.count())}}

But I am getting this error:

[the_coder]

Don’t know BUT have you tested it to see which part of the expresion is breaking?

{{ lookback_gains.sum(“change”) / 3 }}

Are you geting both values back seperately?

{{lookback_gains.sum(“change”) }}

{{ lookback_period_adj.count() }}

I’d start there first and do some shuffling around.

I’m, also NOT familar with Wappler but is count() a function of Wappler? If not try lookback_period_adj.length but Wappler may well have integrated count() into their workflow for all I know, it’s possible to do so.

[patrick]

Seeing the error I think there is some bug in the parsing of the expression, it sees some regexp while there is none.

How about writing it as:

{{ lookback_gains.sum("change") / lookback_period_adj.count() }}

[fatherofinvention]

@patrick

Seeing the error I think there is some bug in the parsing of the expression, it sees some regexp while there is none.

I believe you're right. The only way I am able to get it working is if I break the expression apart into multiple set value steps. So I sum in one step and then divide in the next:

[fatherofinvention]

@the_coder

Don’t know BUT have you tested it to see which part of the expresion is breaking?

Yes, each part of the expression, the sum and the count, return values individually. It is when I attempt to divide the value which is summed using the sum function that is breaks:

{{lookback_gains.sum("change") /2 }}

And produces this error:

"Unterminated RegExp in expression {{lookback_gains.sum("change") /2 }}"

In addition to this bug, the Operation feature does not work. It is not clickable although if you type an expression in that contains an operand, it can parse it, which causes it to show up in the Data Formatter.

Clicking on this does nothing

But typing this {{change > 0}} makes it so the Data Format window displays this:

However, the Select button to save the change does not work and an error is raised about field validation for the value field, even though it contains a value:

So you have to click on Cancel and just rely on what you've entered manually. After saving my action file and testing, I see no issues, everything works as expected, so this leads me to believe that there is a bug on the GUI side that prevents the Operation option from being clicked and also one that throws a validation error, even though there does not appear to be any issue with the logic/syntax.

The outcome of testing is:

Could someone please move these posts into the bug section? If additional detail is required for me in order for you to investigate just let me know!