Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to find the minimum factor across a set time frame (which could change based on selections). This min factor would then be used to adjust the Amount based on the current Factor. The first step however, is to be able to calculate the min factor.
In the example below, I used RangeMin(Factor) for the Min Factor column. As you can see, it's changing with each period but what I really need is for the result to be the Factor for the earliest Period, which is 2.7738.
Hi,
could you put exactly the expected result please ?
if you mean the min factor for location : min(total <Location> Factor)
or is it min for location and prior period ? :
rangemin(above(Factor))
regards
Hi Mike,
do you want it on the frontend editor or in load script?
in load script try this (min for location):
Load
Location,
Min(Factor) as minFactor
From/Resident [table]
Group by Location;
If you want to add to the final table the whole script looks like this:
final:
Load * from/resident [table];
left join(final)
Load
Location,
Min(Factor) as minFactor
From/Resident
In the example above, the Min. Factor should be 2.7738 for all rows since it was the Factor for the earliest Period in the table.
The formula you listed above, "min(total <Location> Factor)", gave a result of 2.7728 which is the lowest of all the Factors in the table. That's close and would be very helpful when I need to expand this calculation to include multiple locations.
I don't think it can be in the Load script since the Period can change based on selections and therefore the Min Factor would need to change.
so
min(total Factor) for all locations !
That gives me the 2.7728 number, which is the min Factor, but not the factor for the min Period, which is 2.7738.
Think I got it to work with this formula:
FirstSortedValue(total <Location> Factor, Period)
ah ok
so :
FirstSortedValue(Factor,- Period) if it suits dimensions
or
FirstSortedValue(Factor,- aggr(only(Period), the dimensions you want))