Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have a simple table like so:
RecordID | Data1 | Data2 |
---|---|---|
1001 | 3 | |
1001 | 5 | 20 |
1001 | 11 | 15 |
1001 | 17 | 5 |
I have a variable, vData1Range=15. I want to get the single value from Data2 where Data1<=vData1Range and where Data1 is the minimum and where Data2 is not null. The result using above example should be 20 (because minimum Data1 that is <=15 and where Data2 is not null is 5, and we can see that when Data1=5, Data2=20.
Thanks in advance!
in a textbox
=SubField(Concat({$ <Data2={">0"},Data1={"<=$(vData1Range)"}>} Data2, ',', Data1), ',', 1)
Or maybe
=FirstSortedValue({<Data2 = {"*"}, Data1 = {"<=$(vData1Range)"} >}Data2, Data1)
(
I think the Data1 filter is maybe not even necessary.
Using FirstSortedValue() may show issues when there are duplicate values of Data1 in your table
)
another one:
=Only({$<Data2={"=Data1=Min({$<Data2={""=Len(Data2)""}>} Total Data1)"}>} Data2)
hope this helps
regards
Marco
Hi Marco, how do I incorporate the value of variable vData1Range in that formula? Value for Data2 should be that of minimum Data1 that is less than or equal to vData1Range.
Value for Data2 should be that of minimum Data1 that is less than or equal to vData1Range.
Isn't this just the minimum value of Data1? Or could your variable threshold be lower than the minimum value of the set of Data1 values?
The relevance of vData1Range is so that it does not look beyond that range. So for example, the minimum Data1 that has a corresponding Data2 value happens to be 20, but vData1Range=15 then the code should not return a value.
So far maxgro's approach above is actually looking good, but testing it further.