sum if of a calculation

[sskinner]

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!

[Vegar]

Try this expression:
count(distinct {<ClientID = {"=Count(distinct ClientHouseID)>=3"}>}ClientID)

[NZFei · Accepted solution]

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

[sskinner]

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.

[sskinner]

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!