topic Expression not working as expected in QlikView

Expression not working as expected

Mon, 26 Jan 2026 18:19:17 GMT

I am having problem with the below expression

=count(if(aggregate<avg({$<MonthEndDate ={$(vMaxSecDate)}>}aggregate),1))

I am using the above expression in a straight table and Sector is my dimension.

Can someone help me what mistake I am doing?

Regards

Re: Expression not working as expected

Tue, 16 Oct 2012 04:09:38 GMT

Please Share your sample document

and what u want to show from that document pls mention clearly

Re: Expression not working as expected

Jason_Michaelides — Tue, 16 Oct 2012 06:48:42 GMT

Firstly, Sum() is faster than Count() so use Sum instead here. Secondly, you have not said what is not right with the expression so this is a pure guess, but try single quotes around the variable:

=Sum(if(aggregate<avg({$<MonthEndDate ={'$(vMaxSecDate)'}>}aggregate),1))

Hope this helps,

Jason

Re: Expression not working as expected

Jason_Michaelides — Tue, 16 Oct 2012 06:53:21 GMT

Actually - looking at this again I think you'll probably need to wrap your Aggregate in Sum() too:

=Sum(if(Sum(aggregate)<avg({$<MonthEndDate ={$(vMaxSecDate)}>}aggregate),1))

Jason

Re: Expression not working as expected

Tue, 16 Oct 2012 15:57:21 GMT

Hi Jason

Thank you for your reply ...

in the below expression

count(if(aggregate<avg({$<MonthEndDate ={$(vMaxSecDate)}>}aggregate),1))

avg({$<MonthEndDate ={$(vMaxSecDate)}>}aggregate) works fine and returns me a value specific to each sector in a straight table.And when I use the same to compare and get the count inseide another expression it returns me nothing.

As you suggested I cannot use sum because I want to compare each individual aggregate value of a sector with the max date value and get the count of those which are less that the max date aggregate value to calculate percentile.

Percetile: Number of aggregate values fall below the current max date aggregate value

-------------------------------------------------------------------------------------------------------

Total number of aggregate value

Please let me know your thoughts...

Re: Expression not working as expected

Jason_Michaelides — Tue, 16 Oct 2012 16:09:02 GMT

avg({$<MonthEndDate ={$(vMaxSecDate)}>}aggregate)

...is an aggregated expression.

Therefore your comparison should also be an aggregated expression. Your dimension is Sector, each of which will most likely contain many rows of fact data so you need to compare the Sum() or Max() or some aggregated version.

Does this not work?

=Sum(if(Sum(aggregate)<avg({$<MonthEndDate ={$(vMaxSecDate)}>}aggregate),1))

Re: Expression not working as expected

Tue, 16 Oct 2012 16:20:42 GMT

No Jason..That doesn't work returns nothing.I am missing something...

Re: Expression not working as expected

Jason_Michaelides — Tue, 16 Oct 2012 16:21:17 GMT

Please post a sample of your app.

Re: Expression not working as expected

Tue, 16 Oct 2012 17:26:00 GMT

Hi Jason,

Please find the attached sample document.

Thanks

Re: Expression not working as expected

Tue, 16 Oct 2012 18:10:03 GMT

Ram I did post a sample document please look at it if you have some time. Thanks

Re: Expression not working as expected

johnw — Tue, 16 Oct 2012 20:05:23 GMT

If I've understood what you want, this seems to work:

count({<Val={"<$(=avg({<Clas={'Advertising'},Date={'$(=max(Date))'}>} Val))"}>} Clas)

Re: Expression not working as expected

Tue, 16 Oct 2012 20:15:49 GMT

Hi John, Thank You

If you see the graph for Advertisng with out any metric (Met) selection max value is 12.12 and number of points that fall below are 6.. so I am trying to get the points below the avg(val) at max date..

Re: Expression not working as expected

johnw — Tue, 16 Oct 2012 20:21:57 GMT

Ah, I misunderstood the requirement. I think you want this:

-sum(aggr(avg(Val)<$(=avg({<Clas={'Advertising'},Date={'$(=max(Date))'}>} Val)),Clas,Date))

Re: Expression not working as expected

Tue, 16 Oct 2012 20:31:44 GMT

Hi John ,

Seems to be working fine for Advertising ..but for other clas in primary clas_type it returns wrong value and also doesn't work for Secondary Clas_type.

Also why are we specifying the 'Advertising' in the expression.

Thanks for your time.

Re: Expression not working as expected

swuehl — Tue, 16 Oct 2012 20:41:58 GMT

Try

=sum(aggr(if(avg(Val)< avg(Total<Clas> {<Date={'$(=Date(max(Date)))'}>} Val),1),Clas,Date))

Regards,

Stefan

Re: Expression not working as expected

Tue, 16 Oct 2012 21:06:48 GMT

Thank You Swuehl !!!