Count - Please Help

[Anonymous]

Hi

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

[swuehl]

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.

[Anonymous]

Sorry it has to be User Friendly Metric Name,

=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)),[User Friendly Metric Name_New]),Order,[User Friendly Metric Name_New]),1,0))

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

[swuehl]

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...

[swuehl]

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.

[swuehl · Accepted solution]

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))

[Anonymous]

great .. thank you so much !!