6 Replies Latest reply: Jan 19, 2016 10:17 AM by patrico mesri RSS

    Table, how to sum by client when values are in different cells

    patrico mesri

      Hi everyone. I need help with 2 excels I'm working on. I started to use the app no more than a month ago, the syntaxis guide and this forum has been really useful but I'm here, stuck again. Found it really hard to make a coherent topic.

      Please, follow the information and you will understand what I want.

       

       

      I have these, 2 excels with the following info:

       

      client table

      customerclient sub#
      coca41
      pepsi32
      sprite53
      fanta64
      mirinda75
      water86

       

      AND


      Sales table:

       

      client nameclient sales Q
      coca444
      pepsi330
      sprite5493
      fanta62
      mirinda72
      water813
      coca434
      pepsi3413
      sprite5405
      fanta640
      mirinda7300
      water81050
      coca4100500
      pepsi394

       

       

      The client name in the Sales table is pretty much endless because it does not sum the "sales Q" column by client.

       

      What I want to show in my table in Qlik Sense is the following:

       

       

      client nameclient sales Q
      coca45444
      pepsi34430
      sprite52493
      fanta6442
      mirinda713232
      water8413

       

      I tried a simple thing which was sum("sales Q") but it says Invalid Dimension. Maybe I should not be using the table or I'm doing it wrong....

       

       

      Now. something else, I managed to paint the "sales Q" cells in Qlik Sense.

       

      if(sum("sales Q")> 3000, 'green()', blue())

      But I want to paint them by client, example:

      If pepsi or coca is > 3000, red, blue

      if sprite, fanta, mirinda, water are > 250, green, red.

       

      How can I write this in the formula?

       

      Thank you in advance.

      Regards.