Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Expression not working as expected

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Expression not working as expected

Try

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

Regards,

Stefan

15 Replies
Not applicable

Re: Expression not working as expected

Please Share your sample document

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

jason_michaelid
Honored Contributor II

Re: Expression not working as expected

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

jason_michaelid
Honored Contributor II

Re: Expression not working as expected

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

Not applicable

Re: Expression not working as expected

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

jason_michaelid
Honored Contributor II

Re: Expression not working as expected

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

Not applicable

Re: Expression not working as expected

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

jason_michaelid
Honored Contributor II

Re: Expression not working as expected

Please post a sample of your app.

Not applicable

Re: Expression not working as expected

Hi Jason,

Please find the attached sample document.

Thanks

Not applicable

Re: Expression not working as expected

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

Community Browser