Complex calculated dimension

[Anonymous]

Hi

Please can someone provide some guidance on whether you think the following is doable?

Any help would be greatly appreciated.

I have a huge number of portfolios and benchmarks with monthly returns.

I've created a histogram of monthly returns by including Floor(Return,0.01) as a dimension and Count(Floor(Return,0.01)) as the expression. This appears to work beautifully.

I would now like to do a similar thing for active returns which I will define simply as Return - BmReturn i.e. portfolio return minus benchmark return.

Please note that my model allows you to select numerous portfolios simultaneously and any benchmark you want i.e. they are not tied and hence can't be specified up front in script.

It seems to be too intensive to do the same thing as above (for the returns) i.e. specifying the dimension as Floor(Return-BmReturn,0.01) and then Count(Floor(Return-BmReturn,0.01)) as the expression. While the first histogram runs 50 portfolios instantaneously, the second takes over a minute just to run one portfolio.

Any ideas please?

Thanks.

Joao.

[Jason_Michaelides]

Oi should be able to improve things by calculating the Floor() of Return and BmReturn in the script. Also, consider adding a field in the script like:

1 AS %Counter_Returns

Also, I can't see why you can't calculate Return-BmRetun in the script as it appears to be a row-level calculation without any aggregations that would depend on user selections.

Floor(Return-BmReturn) AS MyDimension

After doing all of these things, your dimension could be MyDimension, and your expression Sum(%Counter_Returns)

Hope this helps,

Jason

[Anonymous]

Thanks Jason.

The problem with calculating floor of return and bmreturn in script is that I want the floor of the difference, and taking the floor of each first can yield a different answer i.e. although floor(return-bmreturn) can equal floor(return)-floor(bmreturn), it doesn't always do so.

I have no idea what you second suggestion is i.e. what does %Counter_Returns do?

The only way I could do the calculation in the script as you suggest, would be to do it for every combination i.e. every portfolio with every benchmark. Given that I have over a thousand portfolios and benchmarks, this would lead to more than a million combinations. Given that each will have an average of 60 months of returns, should take it to over 60 million rows. Probably not a problem, but may not be ideal. I would then need to find a way to link based on multiple selections.

Any other ideas? It looks like my options may be limited??

Thanks again.

Joao.

[Jason_Michaelides]

Joao,

From your description in the original post it looks like Floor(Return-BmReturn) is a row (record) - level calculation, i.e. the subtraction is always done on the same row in the data set. If it was dependent upon combinations of selections as you say it is I would expect something more like Floor(Sum(Return)-Sum(BmReturn)). But maybe I'm misunderstanding you. If you can provide an sample application it would be easier to assist you further.

Cheers,

Jason

[Anonymous]

Thanks Jason.

I'm fairly new to Qlikview and not completely up to speed with the terminology, so apologies for any confusion.

Essentially, I have a table that has the portfolio name, return date and return for thousands of portfolios and dates. Similarly for benchmarks which is actually just a duplicate of the same table with different field names which I link on return date.

When you select multiple portfolios, I use the portfolio name as the first dimension to create chart trellis of a histogram for each of the selected portfolios, so I'm not wanting to sum across the portfolio name dimension.

I hope this provides some clarity.

Sent from my iPad

[Jason_Michaelides]

Joao,

When asking for help on this forum you need to provide as much information as possible so that the regular contributors are able to understand you and help quickly. Often, we squeeze in responses to our already busy working days!  It is much easier to help if you can provide a sample application, or at the very least some sample data.  If you can do this, I will be able to help further - without it, it is largely guesswork...

Cheers,

Jason

[Anonymous]

Thanks Jason, and apologies for making this more difficult than necessary. I will try to extract some sample data to include in the post.

Sent from my iPad