Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have a problem which I can't solve: I have a set of instruments for which I calculate the performance over a period of time. In a next step, I would like to count all instruments withh a positive performance. When I enter the following formula:
count(distinct(if(exp(sum({<Date= {">=$(=StartDate)<=$(=EndDate)"}>} total Aggr(log(sum(EoP_PX)/sum(BoP_PX)),Date)))-1<0,[Instr ID])))
it doesn't limit on positive values only but gives me the overall number of instruments. I can't figure out what is wrong here. Can someone please help?
Thank you very much!
Laila
Hi,
Ok, this is my final try, otherwise, I'll just be more of a hindrance than a help.
I've changed the formulae around a bit, so hopefully I haven't totally messed up your expression!!
Thanks,
Azam
Hi,
The expression is a bit complicated for me to try and reproduce, but just breaking it down it reads:
Count distinct [Instr ID] if (complicated sum minus 1) is less than 0.
Removing the (complicated sum) expression and just plugging in some positive and negative values did return a result; however as the if statement is testing against less than zero right at the end, it is just counts those [Instr ID] with -ve values.
Does that help? If I've misunderstood, then perhaps you can provide some results for:
exp(sum({<Date= {">=$(=StartDate)<=$(=EndDate)"}>} total Aggr(log(sum(EoP_PX)/sum(BoP_PX)),Date)))
Thanks,
Azam
hi Azam,
thank you very much for the reply.
You understand completely right. What the formula does is that it calculates the performance of instruments between a start and end date (which can be set by the user), by adding up the logarithmic daily performances up to that day with each other.
I have the impression that the 'total aggr' might be the problem, but I just can't figure it out.
kind regards,
Laila
Hello
Are StartDate and EndDate variables or fields?
If they are variables :
count(distinct(if(exp(sum({<Date= {">=$(StartDate)<=$(EndDate)"}>} total Aggr(log(sum(EoP_PX)/sum(BoP_PX)),Date)))-1<0,[Instr ID])))
Else if they are fields try adding min and max functions to the StartDate and EndDate.
count(distinct(if(exp(sum({<Date= {">=$(=min(StartDate))<=$(=max(EndDate))"}>} total Aggr(log(sum(EoP_PX)/sum(BoP_PX)),Date)))-1<0,[Instr ID])))
It will be helpful if you could attach the app with sample data.
I hope this helps!
Regards
MultiView
Hi,
I've had to simplify things a bit, but I think the text box has the expression that you need (although you need to re-do the set analysis for dates and add your log expression etc).
I think the main thing is that you need to aggregate over 2 dimensions: the date and the instrument ID.
Let me know how you get on,
Azam
Message was edited by: a.mullick
dear both,
I tried your solutions, but it is still not working. Mullviews solution doesn't filter for some reasons and when I apply Azams solution it gives me an error.
I guess it has something to do with the calculation of the performance. the dates are variables. I attached a simplified app, maybe this will clarify it a bit.
Anyway, thank you very much for your help so far.
Kind regards
Hi,
Ok, this is my final try, otherwise, I'll just be more of a hindrance than a help.
I've changed the formulae around a bit, so hopefully I haven't totally messed up your expression!!
Thanks,
Azam
You are the man!!!
That was exactly it!
Thank you very much for the help, this is awesome!
Kind regards,
Laila