How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

czajw
How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row? I mean analogies to the operation of Excel function: SUMIF

Here in this form I'm trying to calculate:

https://demo.orbeon.com/demo/fr/orbeon/builder/edit/9bf3b2d64bdc2619588b5646b886f28bfa15f10b

How compute the sum of the control if and only if choice-control is 1 ( First choice) or 2 (Second choice)

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/405a395c-449e-427d-a1df-3cd4d44036dfn%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

Alessandro  Vernet
Administrator
Hi Vlad,

The following expression will do the trick:

    sum(
        $a[
            string() castable as xs:decimal and
            ../choice-control = ('1', '2')
        ],
        0.0
    )

You can read the `[…]` as "such as". And the first part of the condition is
to skip over non-decimal values (empty or invalid). You'll let me know if
this works for you!

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/1612465460390-0.post%40n4.nabble.com.
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

czajw
Thanks, Alex. This works for one field with checkboxes / radio buttons. And how will it be in a situation where there will be more such fields?

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/7882d52b-2ab2-435a-b259-840291233e6an%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

Alessandro  Vernet
Administrator
Hi Vlad,

I am not sure to follow: in the example below the sum is done over several
rows. So here the sum is 5, that is 2+3, and the 1 on the last row is not
counted since one of the first 2 radio buttons are not selected. If you have
a different case, could you illustrate it in an example on demo?

sumif.png <http://discuss.orbeon.com/file/t119778/sumif.png>  

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/1612895705614-0.post%40n4.nabble.com.
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

czajw
I mean a similar situation in repeated section (section 3 in demo https://demo.orbeon.com/demo/fr/orbeon/builder/edit/9bf3b2d64bdc2619588b5646b886f28bfa15f10b ), but when there are two fields from radiobutton / checkbox (one field in the repetaed grid and one outside)

And for example: We sum up as above, but there is also a choice in 'choice-control-3' e.g. $ choice-control-3 = 1

I tried to calculate in section 4 in demo, but not working

wtorek, 9 lutego 2021 o 19:35:10 UTC+1 Alessandro Vernet napisał(a):
Hi Vlad,

I am not sure to follow: in the example below the sum is done over several
rows. So here the sum is 5, that is 2+3, and the 1 on the last row is not
counted since one of the first 2 radio buttons are not selected. If you have
a different case, could you illustrate it in an example on demo?

sumif.png <http://discuss.orbeon.com/file/t119778/sumif.png>

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/e6733332-3082-4698-8c21-8b2c93dd8e4dn%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

Alessandro  Vernet
Administrator
Hi Vlad,

Got it. This indeed gets a bit more complicated:

    sum(
        $b[
            string() castable as xs:decimal and
            ../choice-control-2 = ('1', '2') and
            ../../..//choice-control-3 = '1'
        ],
        0.0
    )

Now, we don't like those path expressions, because, beyond being quite
complex, they create a dependency from your form to the particular data
structure, which may change with future versions of Orbeon Forms. So I've
created an RFE for extending some existing functions, which when implemented
would avoid those path expressions. But in the meantime, you'll let me know
if the expression above works for you.

https://github.com/orbeon/orbeon-forms/issues/4771

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/1613180473550-0.post%40n4.nabble.com.
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

czajw
Hi Alex,

This works, but look at this, please:


I have built a form that is similar to the one I am working on.
I have to be 100% sure that this form calculates correctly if I choose different choices in sector and type of position.
I am not sure, what about when I choose type of psoition without sector.
I also built this form so that someone else who has a similar problem will be able to use it.
You've helped me a lot, really. Thank you.

Vlad

sobota, 13 lutego 2021 o 02:41:15 UTC+1 Alessandro Vernet napisał(a):
Hi Vlad,

Got it. This indeed gets a bit more complicated:

sum(
$b[
string() castable as xs:decimal and
../choice-control-2 = ('1', '2') and
../../..//choice-control-3 = '1'
],
0.0
)

Now, we don't like those path expressions, because, beyond being quite
complex, they create a dependency from your form to the particular data
structure, which may change with future versions of Orbeon Forms. So I've
created an RFE for extending some existing functions, which when implemented
would avoid those path expressions. But in the meantime, you'll let me know
if the expression above works for you.

https://github.com/orbeon/orbeon-forms/issues/4771

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/bca273e1-e406-4a74-8b1d-5108a79cf06en%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

czajw
I mean 'length_service_control-3'

wtorek, 16 lutego 2021 o 12:59:34 UTC+1 Władysław Czaja napisał(a):
Hi Alex,

This works, but look at this, please:


I have built a form that is similar to the one I am working on.
I have to be 100% sure that this form calculates correctly if I choose different choices in sector and type of position.
I am not sure, what about when I choose type of psoition without sector.
I also built this form so that someone else who has a similar problem will be able to use it.
You've helped me a lot, really. Thank you.

Vlad

sobota, 13 lutego 2021 o 02:41:15 UTC+1 Alessandro Vernet napisał(a):
Hi Vlad,

Got it. This indeed gets a bit more complicated:

sum(
$b[
string() castable as xs:decimal and
../choice-control-2 = ('1', '2') and
../../..//choice-control-3 = '1'
],
0.0
)

Now, we don't like those path expressions, because, beyond being quite
complex, they create a dependency from your form to the particular data
structure, which may change with future versions of Orbeon Forms. So I've
created an RFE for extending some existing functions, which when implemented
would avoid those path expressions. But in the meantime, you'll let me know
if the expression above works for you.

https://github.com/orbeon/orbeon-forms/issues/4771

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/e88d90fa-5c4d-4fb8-9560-dcda10f8dc29n%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

czajw
It seems to me that even if at the beginning it calculates well, then when I change the values in type of position Orbeon does not calculate

wtorek, 16 lutego 2021 o 15:30:46 UTC+1 Władysław Czaja napisał(a):
I mean 'length_service_control-3'

wtorek, 16 lutego 2021 o 12:59:34 UTC+1 Władysław Czaja napisał(a):
Hi Alex,

This works, but look at this, please:


I have built a form that is similar to the one I am working on.
I have to be 100% sure that this form calculates correctly if I choose different choices in sector and type of position.
I am not sure, what about when I choose type of psoition without sector.
I also built this form so that someone else who has a similar problem will be able to use it.
You've helped me a lot, really. Thank you.

Vlad

sobota, 13 lutego 2021 o 02:41:15 UTC+1 Alessandro Vernet napisał(a):
Hi Vlad,

Got it. This indeed gets a bit more complicated:

sum(
$b[
string() castable as xs:decimal and
../choice-control-2 = ('1', '2') and
../../..//choice-control-3 = '1'
],
0.0
)

Now, we don't like those path expressions, because, beyond being quite
complex, they create a dependency from your form to the particular data
structure, which may change with future versions of Orbeon Forms. So I've
created an RFE for extending some existing functions, which when implemented
would avoid those path expressions. But in the meantime, you'll let me know
if the expression above works for you.

https://github.com/orbeon/orbeon-forms/issues/4771

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/b3a2f787-4472-4f05-ae63-76f7a476a095n%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

Alessandro  Vernet
Administrator
Hi Vlad,

Thank you for putting the form together, but what are exactly the steps I
should follow to reproduce the problem? What is the behavior you're seeing
when following those steps, and what is the behavior you were expecting?

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/1613591637326-0.post%40n4.nabble.com.
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

czajw
Hi Alex,

For example:
length of service if type of position = 1 we have here : 3 (2+1) (manager is in two lines)
length of service if type of position = 2 we have here:  0 (no specialists)

1.PNG

and here ( I changed manager into specialist in second row):

length of service if type of position = 1 we have here : 3 (but manager is now in only in first line, so it should change into 2)
length of service if type of position = 2 we have here: 3 (specialists in second row, , so it should change into 1)
length of service if type of position =1 and sector = 1,2,3,4,5  - we have here: 3 (but manager is now in only in first line, so it should change into 2)

2.PNG

Formulas there:


in  the third  field of the Calculations section:

sum( $length_service_control[ string() castable as xs:decimal 
and ../../..//type_position = '1' ], 0.0 )

in the fourth field of the Calculations section:

sum( $length_service_control[ string() castable as xs:decimal 
and ../../..//type_position = '2' ], 0.0 )

in the fifth field of the Calculations section:

sum( $length_service_control[ string() castable as xs:decimal and ../sector_control = ('1', '2', '3', '4', '5') and ../../..//type_position = '1' ], 0.0 )

środa, 17 lutego 2021 o 20:54:01 UTC+1 Alessandro Vernet napisał(a):
Hi Vlad,

Thank you for putting the form together, but what are exactly the steps I
should follow to reproduce the problem? What is the behavior you're seeing
when following those steps, and what is the behavior you were expecting?

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/4232497d-1c11-4691-a0d5-adbdbf685797n%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

Alessandro  Vernet
Administrator
Hi Vlad,

In this case you don't have multiple levels of nesting, so
`length_service_control` is at the same level as `type_position`. This means
that your calculation for the 3rd control, which right now is:

    sum($length_service_control[
        string() castable as xs:decimal and
        ../../..//type_position = '1'
    ], 0.0)

Should instead be:

    sum($length_service_control[
        string() castable as xs:decimal and
        ../type_position = '1'
    ], 0.0)

You'll let me know if this makes sense.

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/1613628714297-0.post%40n4.nabble.com.
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

czajw
HI Alex,

Thank you suggestions. 
Nesting is the keyword ;)

When I started comparing the form (because mine was more complex than what I presented here, it turned out that I also had a repeteted grid between the main grid and type of position (so the type of position was in a completely different grid).
And that's why I probably had problems with the calculations.
As I moved the type of position to the main grid, it started to count properly.

In a case like here, it seems to me that:
../../..//type_position = '1' doesn't count properly and ../type_position = '1' also

3.PNG

And by the way, is it possible in Orbeon to place a repeted grid in a grid, or an ordinary grid in a grid (like nesting grids) ?

Vlad

czwartek, 18 lutego 2021 o 07:11:56 UTC+1 Alessandro Vernet napisał(a):
Hi Vlad,

In this case you don't have multiple levels of nesting, so
`length_service_control` is at the same level as `type_position`. This means
that your calculation for the 3rd control, which right now is:

sum($length_service_control[
string() castable as xs:decimal and
../../..//type_position = '1'
], 0.0)

Should instead be:

sum($length_service_control[
string() castable as xs:decimal and
../type_position = '1'
], 0.0)

You'll let me know if this makes sense.

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/d57461b0-c2f0-478d-8c44-4d79796f0021n%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

czajw
Here is example with repeated grid between: https://demo.orbeon.com/demo/fr/orbeon/builder/edit/64354b46f04d18097299ae3e657328e23ca0f401
grid-2//type_position = '1' doesn't count properly 


bookstore//book - Selects all book elements that are descendant of the bookstore element, no matter where they are under the bookstore element
czwartek, 18 lutego 2021 o 10:23:02 UTC+1 Władysław Czaja napisał(a):
HI Alex,

Thank you suggestions. 
Nesting is the keyword ;)

When I started comparing the form (because mine was more complex than what I presented here, it turned out that I also had a repeteted grid between the main grid and type of position (so the type of position was in a completely different grid).
And that's why I probably had problems with the calculations.
As I moved the type of position to the main grid, it started to count properly.

In a case like here, it seems to me that:
../../..//type_position = '1' doesn't count properly and ../type_position = '1' also

3.PNG

And by the way, is it possible in Orbeon to place a repeted grid in a grid, or an ordinary grid in a grid (like nesting grids) ?

Vlad

czwartek, 18 lutego 2021 o 07:11:56 UTC+1 Alessandro Vernet napisał(a):
Hi Vlad,

In this case you don't have multiple levels of nesting, so
`length_service_control` is at the same level as `type_position`. This means
that your calculation for the 3rd control, which right now is:

sum($length_service_control[
string() castable as xs:decimal and
../../..//type_position = '1'
], 0.0)

Should instead be:

sum($length_service_control[
string() castable as xs:decimal and
../type_position = '1'
], 0.0)

You'll let me know if this makes sense.

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/2293bd9c-da2b-4997-85f6-880b3728a8d7n%40googlegroups.com.
Reply | Threaded
Open this post in threaded view
|

Re: How to add up the values ​​in rows repeated grid / section depending on checkbox / radio button selection in another control, but on the same row?

Alessandro  Vernet
Administrator
In reply to this post by czajw
Hi Vlad,


czajw wrote
> And by the way, is it possible in Orbeon to place a repeted grid in a
> grid, or an ordinary grid in a grid (like nesting grids) ?

It isn't: you cannot nest grids.


czajw wrote
> Here is example with repeated grid between:
> https://demo.orbeon.com/demo/fr/orbeon/builder/edit/64354b46f04d18097299ae3e657328e23ca0f401
> grid-2//type_position = '1' doesn't count properly

You really need to tell us what to do to reproduce the problem, what the
observed behavior, and what the desired behavior are. Don't leave me trying
to guess what field you're referring to and what you're trying to do. Also,
if possible, try to create examples that are as simple as possible to
reproduce the problem (with the least amount of fields, etc). Yes, I know,
it's a lot to ask, so thank you for helping!

-Alex

-----
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet
--
Sent from: http://discuss.orbeon.com/

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/orbeon/1613764211762-0.post%40n4.nabble.com.
--
Follow Orbeon on Twitter: @orbeon
Follow me on Twitter: @avernet