topic Re: Find minimum in Visualization and Usability

Find minimum

nikonmike — Tue, 28 Aug 2018 14:53:45 GMT

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.

Re: Find minimum

ogautier62 — Tue, 28 Aug 2018 15:02:17 GMT

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

Re: Find minimum

undergrinder — Tue, 28 Aug 2018 15:05:26 GMT

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

Group by Location;

Re: Find minimum

nikonmike — Tue, 28 Aug 2018 15:11:12 GMT

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.

Re: Find minimum

nikonmike — Tue, 28 Aug 2018 15:12:27 GMT

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.

Re: Find minimum

ogautier62 — Tue, 28 Aug 2018 15:15:00 GMT

min(total Factor) for all locations !

Re: Find minimum

nikonmike — Tue, 28 Aug 2018 15:22:21 GMT

That gives me the 2.7728 number, which is the min Factor, but not the factor for the min Period, which is 2.7738.

Re: Find minimum

nikonmike — Tue, 28 Aug 2018 15:42:57 GMT

Think I got it to work with this formula:

FirstSortedValue(total <Location> Factor, Period)

Re: Find minimum

ogautier62 — Tue, 28 Aug 2018 15:45:04 GMT

ah ok

so :

FirstSortedValue(Factor,- Period) if it suits dimensions

FirstSortedValue(Factor,- aggr(only(Period), the dimensions you want))