RIGHT() formula help and already exists?

21

@Joe_Innes

Hi Joe

Been building my table using the vat rates formula above but it throws up an error when the percentage is anything other than the last rate.

The error is: “Cannot convert value to a specified format”

Any ideas?

Thanks

22

Hi Juan,

The column works correctly, it’s just the little pink triangle and the error displays when you hover over, right?

This is because actually I’m returning two different data types from the formula, one is a percentage, the other is actually a row reference.

If you use the formula below, the error should disappear:

If([VAT Rates].FormulaMap(If(CurrentValue.[Start Date] <= thisRow.Date And CurrentValue.[End Date] >= thisRow.Date, CurrentValue,false)).filter(CurrentValue).Count() < 1, [VAT Rates].Sort(false(), [Start Date]).Rate.First() , [VAT Rates].FormulaMap(If(CurrentValue.[Start Date] <= thisRow.Date And CurrentValue.[End Date] >= thisRow.Date, CurrentValue.Rate,false)).Filter(CurrentValue))
23

Hi Joe

It did display the correct value but when you try to use that value in a calculation it won’t display anything other than if the value was 20%.

You new calculation seems to work perfectly, thank you.

I still can’t quite work out how it works though, I tried taking it apart but kept getting errors.

I think the docs need more examples to go with the formulas to help newbies like myself.

1 Like
24

Just been playing about with the above formula and it appears that if the date matches the start date exactly, the rate is 20%.

Feels like the = or greater is not working and only greater than is working?

Heres the code:

If([VAT Rates].FormulaMap(If(CurrentValue.[Start Date] <= thisRow.Date And CurrentValue.[End Date] >= thisRow.Date, CurrentValue,false)).filter(CurrentValue).Count() < 1, [VAT Rates].Sort(false(), [Start Date]).Rate.First() , [VAT Rates].FormulaMap(If(CurrentValue.[Start Date] <= thisRow.Date And CurrentValue.[End Date] >= thisRow.Date, CurrentValue.Rate,false)).Filter(CurrentValue))