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

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

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.

I have these, 2 excels with the following info:

client table

 customer client sub# coca 4 1 pepsi 3 2 sprite 5 3 fanta 6 4 mirinda 7 5 water 8 6

AND

Sales table:

 client name client sales Q coca 4 44 pepsi 3 30 sprite 5 493 fanta 6 2 mirinda 7 2 water 8 13 coca 4 34 pepsi 3 413 sprite 5 405 fanta 6 40 mirinda 7 300 water 8 1050 coca 4 100500 pepsi 3 94

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 name client sales Q coca 4 5444 pepsi 3 4430 sprite 5 2493 fanta 6 442 mirinda 7 13232 water 8 413

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?

Regards.

• ###### Re: Table, how to sum by client when values are in different cells

1) Create a chart with dimension "client name"

1b) If needed, create a second dimension "client"

2) Create an expression

=Sum("sales Q")

• ###### Re: Table, how to sum by client when values are in different cells

I said I did that sum but it gives me Invalid Dimension.

• ###### Re: Table, how to sum by client when values are in different cells

You just need to select the appropriate field name from the drop down list.

Where does it give you an error, could you post your app or screenshots?

• ###### Re: Table, how to sum by client when values are in different cells

Is this you want?

Color expression goes here:

Color expression:

If(Match([client name], 'coca', 'pepsi'),

If(Sum([sales Q])> 3000, Red(), Blue()),

If(Sum([sales Q]) > 250, Green(), Red()))

• ###### Re: Table, how to sum by client when values are in different cells

Thank you Sunny-T

• ###### Re: Table, how to sum by client when values are in different cells

Hi Patricio,

You can even make use of the ColorMix() function if you have more complex calculations while coloring.

Thanks,

Sangram Reddy.