Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Highlighted
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
agigliotti
Honored Contributor II

Re: Sum numbers and distinct value

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

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
agigliotti
Honored Contributor II

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

agigliotti
Honored Contributor II

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

agigliotti
Honored Contributor II

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 Smiley Happy

 

Community Browser