topic Re: Count - Please Help in QlikView

Count - Please Help

Anonymous — Wed, 11 Nov 2015 19:26:00 GMT

I have 2 expressions ,

expression 1 : =only({<CLNDR_DT={'$(=vSelectedDate)'}, [User Friendly Metric Name]=, Date=, [Metric Type]=>} Value )

expression 2 : =only({<CLNDR_DT={'$(=vSelectedDate)'}, [User Friendly Metric Name]=, Date=, [Metric Type]=>} BridgeValue )

I need to count the number of records where result of expression 1 is less than result of expression 2 and put it in a text box

here is the current expression I using but this gives me 0 , btw I have an 2 dimensions in the chart =if(left([Metric Type], 4) = 'TP90', [User Friendly Metric Name]) and Order , how do I explicitly mention these in the text box expressionn

=sum((if(aggr(count(only({<CLNDR_DT={'$(=vSelectedDate)'}, [User Friendly Metric Name]=, Date=, [Metric Type]=>} Value ) <=
only({<CLNDR_DT={'$(=vSelectedDate)'}, [User Friendly Metric Name]=, Date=, [Metric Type]=>} BridgeValue)),AllSLAViolations.Metric),1,0)))

can some one please help ,its urgent

Thanks

Re: Count - Please Help

swuehl — Wed, 11 Nov 2015 21:30:57 GMT

what is this:

,AllSLAViolations.Metric

And why are you not using your chart dimensions in the aggr() function as dimensions (well, you can't use the calculated dimension as aggr() dimension, but you should be able to create a calculated field in the script to replace both the chart dimension as well as the aggr() dimenson.

Re: Count - Please Help

Anonymous — Wed, 11 Nov 2015 22:11:51 GMT

Sorry it has to be User Friendly Metric Name,

How do I include dimensions in the above expression ?the above is not working can you please correct it

Re: Count - Please Help

swuehl — Wed, 11 Nov 2015 22:25:30 GMT

Try

=sum((if(left([Metric Type], 4) = 'TP90' AND aggr(count(only({<CLNDR_DT={'$(=vSelectedDate)'}, [User Friendly Metric Name]=, Date=, [Metric Type]=>} Value ) <=
only({<CLNDR_DT={'$(=vSelectedDate)'}, [User Friendly Metric Name]=, Date=, [Metric Type]=>}BridgeValue)),[User Friendly Metric Name], Order),1,0)))

It would be much easier if you could upload a small sample QVW...

Re: Count - Please Help

swuehl — Wed, 11 Nov 2015 22:35:13 GMT

Reply to the thread, but not from the inbox, follow

Count - Please Help

Then click on 'Use advanced editor' on the upper right corner of the editor, you'll see an attach button.

Re: Count - Please Help

swuehl — Wed, 11 Nov 2015 22:40:20 GMT

This seems to return the correct value of 5:

=sum({<CLNDR_DT={'$(=vSelectedDate)'}, [User Friendly Metric Name]=, Date=, [Metric Type]=>}

if(left([Metric Type], 4) = 'TP90' AND aggr(only({<CLNDR_DT={'$(=vSelectedDate)'}, [User Friendly Metric Name]=, Date=, [Metric Type]=>} Value ) <=

only({<CLNDR_DT={'$(=vSelectedDate)'}, [User Friendly Metric Name]=, Date=, [Metric Type]=>}BridgeValue),[User Friendly Metric Name], Order),1,0))

Re: Count - Please Help

Anonymous — Wed, 11 Nov 2015 22:41:45 GMT

great .. thank you so much !!