计算不适用于Switch或IIF功能 [英] Calculation not working for Switch or IIF Function

问题描述

我在查询中创建了计算，该计算根据性能预期返回佣金支付。但如果我参考的表格中的表现期望值超过100，则我的计算不起作用。 

I have created calculations in a query which returns a commission payout based on performance expectations. But if the Performance Expectation is more than 100 in the table I am referencing, my calculation does not work. 

有3个性能预期，并且您需要支付百分之一的费用佣金取决于您的效果评分。 

There are 3 performance expectations, and you are paid a percentage of a commission depending on what your performance rating is. 

例如：节省付款目标（$ 1000）

For example: Save Rate Payout Target ($1000)

< 72 = 0％付款

<72 = 0% Payout

< = 75.9 = 50％付款

<=75.9 = 50% Payout

< ='80.9 = 75％付款

<='80.9 = 75% Payout

< = 84.9 = 100％支付

<=84.9 = 100% Payout

> = 85 = 150％支付

>=85 = 150% Payout

如果员工有71保存率，他们会得到$ 0支付。如果他们有99.9，他们将获得1500美元。我的问题是，如果他们的保存率为100，他们应该得到1500美元，但我的查询计算结果为$ 0.

If the employee has a 71 Save rate, they would get $0 payout. If they have 99.9, they would get $1500. My issue is that if they have a Save Rate of 100, they should get $1500, but my query calculation is resulting it as $0.

我也尝试使用切换功能，我最终得到了相同的结果。

I have tried to use a Switch Function as well and I ended up with the same results.

保存基数是我的查询中的一个计算，我必须参考，但为了尝试简化我的问题，我刚刚说明目标金额为$ 1000。

Save Base is a calculation in my query that I have to reference, but to try and simplify my question, I've just stated the target amount as $1000.

SaveAmount：IIf（[保存率]<'72'，[保存基数] * 0，IIf（[保存率]< ='75.9'，[保存基数] * 0.5，IIf（[保存率]< ='80.9'，[保存基数] * 0.75，IIf（[保存 

率]< =''84.9'，[保存基数] * IIf（1，[保存率]> ='85'，[保存基数] * 1.5）））））

SaveAmount: IIf([Save Rate]<'72',[Save Base]*0,IIf([Save Rate]<='75.9',[Save Base]*0.5,IIf([Save Rate]<='80.9',[Save Base]*0.75,IIf([Save 
Rate]<='84.9',[Save Base]*IIf(1,[Save Rate]>='85',[Save Base]*1.5)))))

我想知道计算是否正确并且有原始数据格式化的错误。目前它是短文，但我试图将其更改为数字和货币。

I'm wondering if the calculation is correct and there is something that is wrong with the formatting of the raw data. Currently it is Short Text, but I have tried to change it to number and currency.

推荐答案

保存率应为浮点值。现在它是文本，因此"100"是文本。在"85"之前排序

Save rate should be a floating point value. As it stands it is text and so "100" sorts before "85"

一旦你解决了这个问题，1就会大于0.85。

Once you fix that, 1 will be greater than 0.85.

这篇关于计算不适用于Switch或IIF功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！