Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try
=sum(aggr(if(avg(Val)< avg(Total<Clas> {<Date={'$(=Date(max(Date)))'}>} Val),1),Clas,Date))
Regards,
Stefan
Please Share your sample document
and what u want to show from that document pls mention clearly
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
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
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...
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))
No Jason..That doesn't work returns nothing.I am missing something...
Please post a sample of your app.
Hi Jason,
Please find the attached sample document.
Thanks
Ram I did post a sample document please look at it if you have some time. Thanks