topic Re: Count all positive values in QlikView

Count all positive values

Fri, 09 Aug 2013 06:49:00 GMT

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

Re: Count all positive values

a_mullick — Fri, 09 Aug 2013 09:36:52 GMT

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

Fri, 09 Aug 2013 11:04:53 GMT

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

Anonymous — Fri, 09 Aug 2013 12:14:17 GMT

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

a_mullick — Fri, 09 Aug 2013 12:58:23 GMT

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

Re: Count all positive values

Fri, 09 Aug 2013 14:01:20 GMT

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

Re: Count all positive values

a_mullick — Fri, 09 Aug 2013 15:29:48 GMT

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

Re: Count all positive values

Fri, 09 Aug 2013 15:48:06 GMT

You are the man!!!

That was exactly it!

Thank you very much for the help, this is awesome!

Kind regards,

Laila