Expand my Community achievements bar.

report that multiples two columns together based on selected answers?

Avatar

Level 10
Can someone with a bigger brain than I, help me get started on a report that would take the answers from 2 custom fields, assign them with a number value and then show a third column which multiples the two numbers together? e.g. Custom Field 1: Checkboxes showing answers Red, Blue, Yellow (where red = 1, blue = 2, yellow = 3) Custom Field 2: Checkboxes showing answers John, Mary, Sue (where john = 3, mary = 2, sue =3) Report shows Column 1: Custom Field 1 / Column 2: Custom Field 2 / Column 3: Custom Field 1 x Custom Field 2 = some combination of 1, 2, 3 x 3, 2, 1 Otherwise I have to go looking in the advanced reporting manual :). Thank you! PS: Application (in case anyone is looking for ideas) : one of our divisions is doing a request intake form where they plan to score the answers and then multiple the results to give them an indication of priority.
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

3 Replies

Avatar

Level 3
If I'm understanding what you're asking, it'd probably be something to the effect of: Custom expression1: IF({DE:Custom1}="Red",1,IF({DE:Custom1}="Blue",2,IF({DE:Custom1}="Yellow"),3),0) Custom expression2: IF({DE:Custom2}="John",3,IF({DE:Custom2}="Mary",2,IF({DE:Custom2}="Sue"),3),0) Custom expression3: IF({DE:Custom1}="Red",1,IF({DE:Custom1}="Blue",2,IF({DE:Custom1}="Yellow"),3),0) * IF({DE:Custom2}="John",3,IF({DE:Custom2}="Mary",2,IF({DE:Custom2}="Sue"),3),0) So, basically, you just need to repeat the entire expression of each column you want to multiply.

Avatar

Level 3
PS - I just noticed it wrapped the text for Custom Expression 3 when I posted; it should all be on one line.

Avatar

Level 10
thanks Kathy! That was exactly what I needed! This saved me a couple of days of thrashing around in the manual, and I so appreciate you taking a few minutes to quickly answer. For anyone who is silently looking on and wanting to do the same thing, I cleaned up the code a little bit. It basically only needs to look like this: IF(Custom1="Red",1,IF(Custom1="Blue",2,IF(Custom1="Yellow",3)),0) * ______etc (removed "DE" references and corrected parentheses at the end. For additional marks, I know my consultant last year taught me a thing to do with "IF(!isblank(Custom1),IF...." that was a bit more elegant, but I get very tired very easily when I work with parens, so I will leave it as an exercise for future admins...) Thank you thank you thank you! :)