
Re: Count all positive values
Azam Mullick Aug 9, 2013 5:36 AM (in response to lailagrasso)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

Re: Count all positive values
lailagrasso Aug 9, 2013 7:04 AM (in response to Azam Mullick)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

Re: Count all positive values
Yaseen Pullissery Aug 9, 2013 8:14 AM (in response to lailagrasso)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



Re: Count all positive values
Azam Mullick Aug 9, 2013 9:02 AM (in response to lailagrasso)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 redo 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

Count Positive.qvw 135.5 K

Re: Count all positive values
lailagrasso Aug 9, 2013 10:01 AM (in response to Azam 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

QV_Perf_Test.qvw 2.8 MB

Re: Count all positive values
Azam Mullick Aug 9, 2013 11:30 AM (in response to lailagrasso)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

Count Positive V2.qvw 2.8 MB

Re: Count all positive values
lailagrasso Aug 9, 2013 11:48 AM (in response to Azam Mullick)You are the man!!!
That was exactly it!
Thank you very much for the help, this is awesome!
Kind regards,
Laila


