Forum Discussion

AudraMickles's avatar
AudraMickles
Qrew Trainee
4 days ago
Solved

Report Formula - Combine some, exclude some, and include others.

I have a field [Types].  there can be A, B, C, D, E, or F.

I need a summary report about these [Types]  but the output needs to show, A, B, C+D, and exclude E and F.  I'm using filters to exclude E and F.

So I'm trying to use the Report Formula to create a new field [Performance] and using an If statement to say:

if( [Type]="A","A", [Type]="B","B", [Type]="C","G", [Type]="D","G") 

The hope was that by renaming C and D to G, it would add those counts together when I add this Report Formula to the Column Group.  but its only adding "C" not "D".    Is this possible and I just have the wrong formula, or am I just going about this all the wrong way?

Thanks,

A

formulas
  • Mez's avatar
    Mez
    4 days ago

    Is this report formula being used in a summary report? What if you use the Case() function instead? Does this produce the desired output?

    Case( [Type], "A", "A", "B", "B", "C", "G", "D", "G" )

  • Mez's avatar
    Mez
    Qrew Cadet
    4 days ago

    Ah, yeah, those pesky leading or trailing spaces. When I get results that don't seem right and I'm building a formula, I will either switch to using length or trim to see if adding this around the field produces the desired result or not. If you have another report formula still available, then use that perform this check so you can still see both of these report formula columns. 

  • AudraMickles's avatar
    AudraMickles
    Qrew Trainee
    4 days ago

    So, i tried that before and it hadn't worked, but when I tried it again this time, doubled checked the spelling on everything and figured out that whoever entered the original list of Types had 2 hanging spaces after the C type.  sigh  so it works!  yay!  but man, what a pain to figure out :/

  • Mez's avatar
    Mez
    Qrew Cadet
    4 days ago

    Is this report formula being used in a summary report? What if you use the Case() function instead? Does this produce the desired output?

    Case( [Type], "A", "A", "B", "B", "C", "G", "D", "G" )