sum of if statement

[Anonymous]

Hi guys!

Thanks for taking the time to read my question and potentially (no pressure! ) figure it out ... I am still learning how to use Qlik View, and have come across an error message that I don't know how to work around.

I currently have two columns of colors.  I would like to be able to write an expression like this:

= sum(if(color1 = color2, 0, if(color2>0, count(color2), 0), )

Basically, this says that if there is a value in color 2, count the number of times that color appears in column2 per column 1. However, do not include the value in the sum where color 1 and color2 are the same. Please see below for an example:

Data:

Color 1   Color 2                    

blue          green              

blue           green    

blue          blue

blue          blue    

blue           green

blue          red

blue          red

blue          green

red              green    

red               green

red               green

red               green

red               red

yellow        yellow

   Result of the expression:

color 1          color 2           result                              color 1               result of sum

blue                green              4                                   blue                    6

blue               blue                  2                                   red                     4

blue               red                    2        =>                        yellow                0

red                 green                4      

red                 red                    1

yellow            yellow                1

** Basically, the idea is that in a pivot table, I will be able to see the sum of times when a color was matched with another color, not itself, and then be able to open the pivot table and see how many times each color was present

Thank you for any and all help!

[sunny_talwar]

May be try this:

Sum(Aggr(If(color1 <> color2, 1, 0), color1, color2))

[MK_QSL]

Create a Straight Table

Dimension

[Color 1]

Tick

Suppress When Value is Null

Show All Values

Expression

COUNT({<[Color 2] = {"=[Color 2]<>[Color 1]"}>}[Color 2])

Presentation Tab

Untick Suppress Zero Values.

[sunny_talwar]

Better expression:

Sum(Aggr(If([Color 1] <> [Color 2], Count([Color 2]), 0), [Color 1], [Color 2]))

[MarcoWedel]

Hi,

maybe one solution could be also:

-Sum(Len([Color 2]) and [Color 1]<>[Color 2])

hope this helps

regards

Marco

[Anonymous]

Thank you everyone!!

[MarcoWedel]

you're welcome

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco