Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Count all positive values

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

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

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

View solution in original post

7 Replies
Highlighted
Creator III
Creator III

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

Highlighted
Not applicable

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

Highlighted
Creator II
Creator II

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

Highlighted
Creator III
Creator III

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

Highlighted
Not applicable

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

Highlighted
Creator III
Creator III

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

View solution in original post

Highlighted
Not applicable

You are the man!!!

That was exactly it!

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

Kind regards,

Laila