pick rating on the fly using calculated score

[Anonymous]

hi guys, looking for some directions here.

I have large QVD which represents financial transactions - 90 million rows and growing. based on some KPIs and complex calculations, I determine overall score for a specific entity. The score is a number from 0 to 100 and it CANNOT be calculated in a load script - depending on user selections, the scores would be different.

with that said, here is my challenge. I am a given a little table that determines a rate for an entity, based on its score - basically that table has low score and high score and a rate from 1 to 5. I need to calculate the score (again on the fly based on user selections) and using that table identify its rate.

Here is a few options I considered:

1) create a data island table for scores/rates and use if thens to pick a rate from there. While it works great on by 10,000 records sample, it is taking forever in my real app.

2) use aggr, set analysis and advanced search expression in it - did not work because aggr is calculated not per row but per chart.

Any other ideas?

[Anonymous]

What is the relation between score and rate?  Can it be something like

0 to 20     1

21 to 40     2

...

If yes, you can try this.

rate = div(score-1,20)+1

If not, maybe enclosed if()

[Anonymous]

thanks for your reply, Michael. the scores are decimal numbers like 87.45 and rates are defined as ranges like 0..56.25 for rate 1, 56.26..63.78 for rate 2 etc. I have to use a lookup table because of that.

rate is determined by statistical calculations on a side - another team is doing that using special software

[Anonymous]

Ok, what about "if":

if(score<=56.25, 1,

     if(score<=63.78, 2,

     if(score<=xx.xx, 3,

     if(score<=yy.yy, 4, 5

))))

I expect it to be slow but hopefully better than with the data island.  You can give it a try on a limited data set and compare.

[Anonymous]

I cannot hard code these numbers as they will be updated once a month (that other department will recalculate the numbers and send them to me) but you gave me an idea to create this if-then expression in my load script and then use as dynamic expression (dollar expansion) in the actual chart objects.

I am a bit pessimistic though due to the volume of data I have but you gave me hope

is this the only way? I understand QV needs to operate with data loaded in data model and it is no SQL when you can join tables on the fly, but there must be a better solution to that.

[Anonymous]

this did not work as i was hoping. while i was able to build if-then statements dynamically in my load script, i also need to use one variable dimension. I used dollar sign expansion but this only work if I manually select dimension value. Apparently as I've just read on a forum, dollar sign expansion is done for the whole chart not per dimension row so this did not work for me.

I am back using data island table but it is terrible slow.

Any other expert opinions? I could do that in Excel in 1 minute using vlookup function and I guess was hoping QV can do something like that on the fly.

[Anonymous]

(I'm back after a few days "off-line".)
I think there should be a better way.  For now, a sugestion.  Instead of pre-building the whole "if" expression in the script, define only the "border" values as variables in the script, in this case it will be something like this:
vOne = 56.25
vTwo = 63.78
vThree = xx.xx
vFour = yy.yy

And the expression will be:
if(score<=vOne, 1,
     if(score<=vTwo, 2,
     if(score<=vThree, 3,
     if(score<=vFour, 4, 5
))))

Maybe it helps...

[Anonymous]

thanks for trying help me but it would not work in my case. I have 8 metrics that i need to score/rate and each one actually has a rate from 1 to 100. i would need to create 800 variables

so far the best solution i found is to use firstsortedvalue with nested aggr - this actually work well but terribly slow. takes 50 seconds to calculate one chart on a very powerful server.

I gave up on if-s idea because dollar expansions do not work on expressions within charts - expression is expanded before chart is calculated hence you cannot refer to chart dimensions.

[#9]

Borys,

interesting case

I have no license, so i cant open qvd (during weekend).

Please, give me in xls your surce tables with sample data, Script, maybe some example of expected result in xls...

Give me some screen's, sample expressions, etc.

Especially show me (eplain me) how variable dimension works in your app

(you told: this did not work as i was hoping. while i was able to build if-then statements dynamically in my load script, i also need to use one variable dimension. I used dollar sign expansion but this only work if I manually select dimension value. Apparently as I've just read on a forum, dollar sign expansion is done for the whole chart not per dimension row so this did not work for me.).

I will also try to help

regards

Darek

[#10]

Borys,

i thought a little about your case.

For me this "second step" of your calculation (determining rate value) should be done as a expression.

If you let me know your data model, your chart dimensions, structure of your rating data (you told, that your rating dictionary has 800 points, you have 8 metrics etc) I'am sure we will find a way how to generate "score to rate transformation function" to have good performance and flexibility

regards

Darek.