Select record based on outcome of expressions

[#1]

Hi,

Looking for a solution for the following case.

It's a scorecard application.

There is a table holding criteria and points to be earned if the criteria is met.

There is a table with customer-discounts based on the total points.

The customer table looks like (per column):

1.customer number

2.number of transactions

3.turnover last year

4.number of employees

5.points for transactions

6.points for turnover

7.points for employees

8. total points: column(5)+column(6)+column(7)

discount table:

max points  -  discount

10   - 0%

20   - 10%

30   - 20%

999 - 25%

now my problem is how to go from the value in column 8 to the discounts table

Column 9 in the table should show the discount for the customer

for example if column 8 gives 24 points, the discount should be 20%

[#2]

Hi,

please check the attached file.

Niranjan.

[#3]

Hi Niranjan,

Thanks for your quick response.

Would it be possible to calculate the discounts on the dashboard, rather than in the load script?

I've already setup the calculations to calculate the points per customer, now I 'only' need to map it to the discounts table.

Johan.

[#4]

Hi,

Yes, you can also caluculate in chart, please check the attached file.

Niranjan

[#5]

Ah, you hard-coded the discounts in an if-formula.

But what we're looking for is to get the discounts from the table, since this can be changed in the base source database. Please check my initial posting for the discounts table.

[#6]

Hi,

I updated the file, please check it.

[swuehl · Accepted solution]

joh_ver,

I think you are looking for a solution without any link between the tables and not using fixed limits in expression.

I think you can achieve that with some kind of lookup logic. I tried this using aggr and FieldValue / FieldIndex. Maybe there is a simpler solution (except changing the script, that would be much simpler, see above).

Please have a look at attached sample.

Regards,

Stefan

P.S: Here's the expression used:

=aggr(nodistinct if(TotalPoints<= maxPoints and TotalPoints>= FieldValue('maxPoints',FieldIndex('maxPoints',maxPoints)-1),

FieldValue('discount',FieldIndex('maxPoints',maxPoints))), maxPoints,TotalPoints)

[#8]

Yeah, the fieldvalue and fieldindex functions did the trick for me.

(Though in my case I didn't need the aggr around it)

In the future I'll try to pre calculate more in the loadscript.

Thanks guys.

One more question 😉

Anyone has an idea how to store the outcome of an expression into a variable? So I can reuse the calculated value in other charts?

something like vTotalPoints = column(8) ?

[swuehl]

I don't think (to my limited knowledge) that you can reference cell values across different charts or set Variables within charts expressions. Would be a nice feature, though.

What you can do is set your variable to the expression and use the variable in column 8 or any other place.

That will not help you get the exact cell value in to the variable, because the charts dimensions are not taken into account at a different place, e.g a text box.

What also could probably work is using macros, I think it is possible to read out cell values from a macro and it should be possible to set variables to the read out value. You could call the macro trigger based, too.

Hope this helps,

Stefan