Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

m_lavova
New Contributor

Sum numbers and distinct value

Dears,

ist there someone who would help me with me problem, please?

I have data in 3 columns: ASIN, STAR and UNITS SHIPED.

I need to select unique values from ASIN and sum UNITS SHIPED, but only for STAR = 1, 2, 3.

 

it looks like this:

ASIN    STAR   UNITS SHIPED
12             1                     2

12            1                     2

13           4                     1

14           2                     3

14           3                    3

the sum that I need is 5.

I tried a lot of possibilities, but it is not work.

 

Thank you in advance.

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Sum numbers and distinct value

what about the below expression:
Sum( {< STAR = {'1','2','3'} >} DISTINCT [UNITS SHIPED] )
?
10 Replies
Partner
Partner

Re: Sum numbers and distinct value

Hi,
You could try with:
Sum( {< STAR = {'1','2','3'} >} TOTAL <ASIN> DISTINCT UNITS SHIPED )
I hope it could helps.
m_lavova
New Contributor

Re: Sum numbers and distinct value

 

 Thank you, but it does not work correctly

when I use your condition, I see not good values

Untitled.png

MVP
MVP

Re: Sum numbers and distinct value

No need for total, just

Sum({<STAR = {'1','2','3'}>} [UNITS SHIPED])
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

Re: Sum numbers and distinct value

with data provided above it does works.
what are dims and measures expressions of your bar chart ?
m_lavova
New Contributor

Re: Sum numbers and distinct value

dimension is only business segment comes from source data (no expression) and as measure I used your expression

Partner
Partner

Re: Sum numbers and distinct value

could you add "business segment" field to your data above to understand better what are you trying to achieve ?
m_lavova
New Contributor

Re: Sum numbers and distinct value

ASIN    STAR   BUSINESS SEGMENT     UNITS SHIPED
12             1                          LED                                 2

12            1                          LED                                2

13           4                          TRAD                             1

14           2                           LUM                            3

14           3                           LUM                           3

I need to have similar result as you can see below, but for ASIN only unique values need to be genarated

Untitled.png

Highlighted
Partner
Partner

Re: Sum numbers and distinct value

what about the below expression:
Sum( {< STAR = {'1','2','3'} >} DISTINCT [UNITS SHIPED] )
?
m_lavova
New Contributor

Re: Sum numbers and distinct value

Thank you very  much,

it works correctly 🙂