Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
ramya_beginner
Contributor

Count - Please Help

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Count - Please Help

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

6 Replies
MVP
MVP

Re: Count - Please Help

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.

ramya_beginner
Contributor

Re: Count - Please Help

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

MVP
MVP

Re: Count - Please Help

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

MVP
MVP

Re: Count - Please Help

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.

MVP
MVP

Re: Count - Please Help

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

ramya_beginner
Contributor

Re: Count - Please Help

great .. thank you so much !!

Community Browser