topic Adding set analysis to count function in QlikView

Adding set analysis to count function

Tue, 31 Jul 2012 15:02:03 GMT

Hello, I have a functioning expression :- count(if(mid(Move_Out_Date,4,2)='08'and (mid(Move_Out_Date,7,4)='2011')and left(Post_Code,4)='SO15','Count'))

I am trying to add a bit of set analysis to this expression and its not liking it, here is what I am trying :-

count(if(mid(Move_Out_Date,4,2)='08'and (mid(Move_Out_Date,7,4)='2011')and left(Post_Code,4)='SO15' and Count({< Installation= {">1"} > } CA),'Count'))

Re: Adding set analysis to count function

Tue, 31 Jul 2012 15:14:38 GMT

try like

count( {< Installation= {">1"} > }if(mid(Move_Out_Date,4,2)='08'and (mid(Move_Out_Date,7,4)='2011')and left(Post_Code,4)='SO15',CA))

Re: Adding set analysis to count function

Tue, 31 Jul 2012 15:26:58 GMT

Qlikview didnt like that, error in expression :-

Re: Adding set analysis to count function

Tue, 31 Jul 2012 15:27:50 GMT

spencer,

Two thing to note:

Count({< Installation= {">1"} > } CA) is not a condition. It would always return true.

Count({< Installation= {">1"} > } CA) is a group funtion (not a row level funtion) which is applied on what seems to be applied on a row operation. This doesnt make sense to QV.

If you are trying to validate this at for a dimension then try aggr() function for the new condition. Like

=Count(if(aggr(Count({< Installation= {">1"} > } CA), CA) > 1,

Count(if(mid(Move_Out_Date,4,2)='08'and (mid(Move_Out_Date,7,4)='2011')and left(Post_Code,4)='SO15',1)),0))

Kiran.

Re: Adding set analysis to count function

Tue, 31 Jul 2012 15:32:41 GMT

This is spitting out Null '-'

Re: Adding set analysis to count function

Tue, 31 Jul 2012 15:39:00 GMT

Try: (i believe there is an extra ',' infront of if in you syntax)

count( {< Installation= {">1"} > }if(mid(Move_Out_Date,4,2)='08'and (mid(Move_Out_Date,7,4)='2011')and left(Post_Code,4)='SO15',CA))