2

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.

Heathenry
  • 21
  • 1
  • 5

3 Answers3

1

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)))
Chris J
  • 938
  • 1
  • 8
  • 27
  • A more "Qlik" way of doing this would be to use set analysis, however that is a bit of a step away from your existing SQL knowledge – Chris J Jun 15 '16 at 08:10
  • Great. This worked. Yeah, I need some sort of set analysis. If statements will slow down quite a bit. – Heathenry Jun 15 '16 at 08:19
  • @SamuelMaile here's the set analysis wizard I use http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa= – Chris J Jun 15 '16 at 08:30
  • @SamuelMaile I know! unless I have a lot of data (million rows plus) I avoid it - might be easier if you sum the three field in the script so you're only selecting on one field, the analysis would be something like... Count({$<*=E({1$(=0)"}>})>}DISTINCT ID) – Chris J Jun 15 '16 at 08:47
  • It was the following: COUNT ({< ID({0"}>}) + P({0"}>}) + P({0"}>}) >} DISTINCT ID I would up vote you, but I'm still a noob. Thanks for the assistance. – Heathenry Jun 20 '16 at 06:14
  • @ChrisJ I think the link is deprected - it casts the "Nested aggregation is not allowed" error with `Count({$}SUM(total_basket))` – CutePoison Aug 26 '20 at 13:54
1

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)
EldadT
  • 912
  • 7
  • 21
1

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

Antoine Stas
  • 229
  • 1
  • 12