Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

patriciousa
Contributor

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.

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.

1 Solution

Accepted Solutions

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

Is this you want?

Capture.PNG

Color expression goes here:

Capture.PNG

Color expression:

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

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

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

6 Replies
MVP
MVP

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

To answer your first question, this should be easy:

1) Create a chart with dimension "client name"

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

2) Create an expression

=Sum("sales Q")

patriciousa
Contributor

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

Is this you want?

Capture.PNG

Color expression goes here:

Capture.PNG

Color expression:

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

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

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

MVP
MVP

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?

reddys310
Honored Contributor II

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.

patriciousa
Contributor

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

Thank you Sunny-T

Community Browser