Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

Try

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

Regards,

Stefan

View solution in original post

15 Replies
Not applicable
Author

Please Share your sample document

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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_Michaelides
Luminary Alumni
Luminary Alumni

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
Author

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_Michaelides
Luminary Alumni
Luminary Alumni

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
Author

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Please post a sample of your app.

Not applicable
Author

Hi Jason,

Please find the attached sample document.

Thanks

Not applicable
Author

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