CODEWITHSUNDEEP
sqloracle-databaseqlikviewqliksense
Heathenry
Heathenry

Reputation: 21

Qlik Sense, Counting distinct with a sum

I am trying to create an expression in qlik sense to get the count the distinct number of ID's where each prod is added up and is greater than 0.

Qlik sense expression so far, but wrong:

sum(aggr(count (DISTINCT ID), PROD1 + PROD2 + PROD3 ))

I'm not too sure how to add to the expression where to add >0 and the year month.

Working sql: 

    select count(distinct ID) as Number
    from tb1 x
    where (x.Prod1 + x.Prod2 + x.Prod3)> 0
    x.Year = '2016/05'

Any help would be great,

Thanks.

Upvotes: 2

Views: 6081

Answers (3)

EldadT
EldadT

Reputation: 932

in your script add the calc field:

rangesum(Prod1,Prod2,Prod3) as Prod_Total

"rangesum" also converts null to 0! if Prod1,Prod2 or Prod3 is null you will get 0 as a total.

In the chart use this calc:

count({<Prod_Total={'>0'}>} Distinct ID)

Upvotes: 1

Antoine Stas
Antoine Stas

Reputation: 229

If you have a date field in your database, you'll need to create a YearMonth field from your date (Date(mydate, 'YYYYMM') as YearMonth) in the data model script and then put this in your expression:

count({<Prod1={'>0'}, Prod2={'>0'}, Prod3={'>0'}, YearMonth={'201605'}>}[distinct ID])

If your field Year in the database is already a yearmonth field, you can do this (but I recommend the first method):

count({<Prod1={'>0'}, Prod2={'>0'}, Prod3={'>0'}, Year={'2016/05'}>}[distinct ID])

You should read this help section from the Qlik site, it's about set analysis

Upvotes: 1

Chris J
Chris J

Reputation: 936

The easiest way is with an if statement, your code

select count(distinct ID) as Number
    from tb1 x
    where (x.Prod1 + x.Prod2 + x.Prod3)> 0
    x.Year = '2016/05'

becomes

count (DISTINCT, if((PROD1 + PROD2 + PROD3)>0,ID)))

Upvotes: 1

Related Questions