Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
sskinner
New Contributor

sum if of a calculation

Hi, in the pivot table below, the Stays column is defined as count(distinct ClientHouseID). This works and subtotals (by year) are fine. My second requirement is to count the number of clients which have 3 or more stays over the year (see 2016, Client 837). I have tried doing the following  if(Count({$}ClientHouseID)>=3,1,0) and get the flag (Stay eposodical column) but cannot get a subtotal off it (see image, does not subtotal correctly). Tried a bunch of variations (and some aggr) with no luck. Any suggestions? Much appreciated!

 

screen.PNG

 

1 Solution

Accepted Solutions
Partner
Partner

Re: sum if of a calculation

sum(aggr(if(count(DISTINCT ClientHouseID)>=3,1,0),Dim1, Dim2, Dim3))

View solution in original post

4 Replies
Partner
Partner

Re: sum if of a calculation

Try this expression:
count(distinct {<ClientID = {"=Count(distinct ClientHouseID)>=3"}>}ClientID)
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Partner
Partner

Re: sum if of a calculation

sum(aggr(if(count(DISTINCT ClientHouseID)>=3,1,0),Dim1, Dim2, Dim3))

View solution in original post

sskinner
New Contributor

Re: sum if of a calculation

Hi,  thank-you for the response. Re first solution (reply); that is doing something but the result does not appear quite right - see the diagram. It is flagging some entries as => 3 which do not appear to be. The totals are good! Note the data is grouped by a few things. Maybe with set-analysis in that formula?  I will investigate the data further as well - it may be my other column is wrong or data not what I expect.

screen2.PNG

 

sskinner
New Contributor

Re: sum if of a calculation

Re AGGR, I got a variation on that to work. It was still not wanting to put subtotals in. I messed with a rowno(0) example I saw in the community and that seems to work. That solution may also work with the 'count' problems I was having. I will investigate more and post the final answer later.  Thanks!  .  Totals correctly as well - I had to set the dimensions appropriately; I think qlik does some sort of inheriting of things in pivot tables, or not  (have not read up on it).  Thanks very much for the help!