Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
sskinner
Contributor II
Contributor II

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
xufei123
Partner
Partner

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

View solution in original post

4 Replies
Vegar
Partner
Partner

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

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

View solution in original post

sskinner
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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!