Tag: range

excel – How do I calculate the average of a measure (moving range) in PowerPivot/PowerBI?

Pivot Table

Some background:

In the pivot table above, I created the measures for “Count”, “Previous Month Count”, and “Moving Range” (monthly change in counts), all of which are working as expected. My main dataset is linked to a calendar table (so the Year and Month dimensions are from the calendar table).

Previous Month Count: = CALCULATE((Count),DATEADD('Calendar'(Date),-1,MONTH))

Moving Range: = ABS((Count)-(Previous Month Count))

However, I am having trouble creating an additional measure which calculates the average of the monthly “Moving Range” over a period of X months. I’ve tried several methods, but the closest I got so far is the following:

Moving Range (3M AVG): = DIVIDE(CALCULATE((Moving Range),DATESINPERIOD('Calendar'(Date),MAX('Calendar'(Date)), -3, MONTH)),3)

Moving Range (11M AVG): = DIVIDE(CALCULATE((Moving Range),DATESINPERIOD('Calendar'(Date),MAX('Calendar'(Date)), -11, MONTH)),11)

The results are shown in their respective columns, but for some reason only some values are correct (highlighted in green), while others are way off. For example, the expected “Moving Range (11M AVG)” for December 2019 is ~69.1 (average of the moving ranges from Feb through December), but my measure is generating 15.6.

I really hope someone can point out what I’m doing wrong or show me an alternate path to the expected result. Many thanks in advance!

postgresql – Composite multicolumn index for geopoint range and numeric range query

I am building an app where the server needs to select rows based on some criteria/filters. One of them is the location of the user and the radius at which the user want’s to see posts and other filters such date range and filter for a value of another column. This is going to be for an ad-hoc event discovery app.

I have read about PostGIS, its geometry,geography types and I know there is a native point datatype. Based on this answer I understood that it is better to order from equality to range columns, even though I feel like geo point column should be the first.

Suppose the following few rows of a simplified events table (disregard the validity position data):

id  event_title                  event_position   event_type  is_public  start_date
    (varchar)                    (point lat/lon)  (smallint)  (boolean)  (timestamptz)
--  ---------------------------  ---------------  ---------   ---------  ----
 1  "John's Party"               (122,35)         0           0          2020-07-05
 2  "Revolution then Starbucks"  (123,30)         1           1          2020-07-06
 3  "Study for math exam"        (120,36)         2           1          2020-07-07
 4  "Party after exam"           (120,36)         1           1          2020-07-08
 5  "Hiking next to the city"    (95,40)          3           1          2020-07-09
 6  "Football match"             (-42,31)         4           1          2020-07-10

Imagine the table contains several thousand records at least, obviously not only 6.

So in this table a user would be able to query public events close to (122,34) by 100km (suppose first three rows fall into this area) and of event types 0, 1 or 2 falling between dates 2020-07-05 and 2020-07-07. The user would get the rows with ID 2 and 3.

This is the query I want to optimize with an appropriate index. My question is, how is it possible to create such an index? I thought about GiST or GIN index but not sure how these could help. Thanks!

dnd 5e – Do opportunity attacks have disadvantage if I use my action to Dodge and then move out of melee range?

Dodge:

Until the start of your next turn, any attack roll made against you has disadvantage if you can see the attacker, and you make Dexterity saving throws with advantage.

Opportunity attacks require attack rolls, so they would all have disadvantage, as long as the attackers were not invisible.

That being said, I would recommend using disengage instead of dodge:

If you take the Disengage action, your movement doesn’t provoke opportunity attacks for the rest of the turn.

Now, instead of disadvantage, your opponents can’t attack at all.

Dodge and disengage both use an action. So you could do either of those for your action and still dash with your bonus action. So disengage would likely be better than dodge, unless there’s a possibility of getting attacked from range by other enemies, then dodge might be helpful for imposing disadvantage on those ranged attacks, unless you remember this clever trick: ranged attacks have disadvantage against a prone target.

You disengage with your action, then bonus action dash and get 60 feet away from your melee attackers. Then, if there are no more melee attackers within 30-40 feet of you, drop prone, and all ranged attacks will have disadvantage. When your turn comes back around, stand up and double dash for 75 feet of movement.

dnd 5e – What range is used to determine the target of the attack made by the creature affected by a Spectator’s Confusion Ray?

The Spectator has an Eye Ray option, Confusion Ray, that says:

[The target] uses its action to make a melee or ranged attack against a randomly determined creature within range.

Unlike the spell Confusion which limits the attack to melee attacks, the Spectator’s victim can be compelled to make a ranged attack.

When determining the random target, are creatures that are outside of the weapon’s normal range but within the weapon’s long range included?

For a specific example, say a Rogue with a light crossbow is hit by the ray and fails their save. On their turn, there are three allies within 80 feet, and the Spectator is 90 feet away. Is there a chance that the Rogue randomly targets the Spectator?

Recommended color range for background color

I am building an online menu for restaurants.

The default background color is a light gray (#f1f3f5). The default text color is black (#555555). Example

Many of the restaurants have asked to use a custom color for the background. At first I said no, because users are not designers and use colors awfully… but after many requests I am starting to investigate a solution.

The idea is to allow the user to pick any color. Then the color is adjusted within a range.

In particular we would like to keep a light background with dark text on it.

So the solution was to use HSL and force L to a minimum of 0.9.

It has a sufficient effect, but I am not 100% satisfied.

Is there any study about the acceptable color range for a full-screen background (saturation, luminance)?

Split date range (timestamp) into equal parts by Month- SQL Server

Experts,
Have a question regarding splitting a date range into equal parts by months including the time part

example – fromdate – 06/29/2020 09:00:00 and todate – 06/29/2021 09:00:00

Want to split this date range into twelve equal parts like below

06/29/2020 09:00:00 - 06/30/2020 12:59:59
07/01/2020 00:00:00 - 07/31/2020 12:59:59
.........
.......
06/01/2021 00:00:00 - 06/29/2021 09:00:00

I cant write recursive CTE as this is a sql synapse module I am running against..

With below query I am able to split the date part, but time part is not coming properly as above.. Please help me as this is a blocking my development

declare @FromTs DATETIME
declare @ToTs DATETIME
SET @FromTs = GetDate()
SET @ToTs = DATEADD(month, 12, @FromTs)

;WITH n(n) AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY (object_id))-1 FROM sys.all_columns
),
d(qi,qrt,qtt,n,f,t,md,bp,ep,rn) AS  
(
    SELECT 
    ,n.n, @FromTs, @ToTs, 
    DATEDIFF(MONTH, @FromTs, @ToTs),
    DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY( @FromTs),  @FromTs)),
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n, 
    DATEADD(DAY, 1-DAY( @FromTs),  @FromTs))))
    FROM n INNER JOIN <datetbl> AS d 
    ON @ToTs >= DATEADD(MONTH, n.n-1, @FromTs)
)
SELECT qi,qrt,qtt,
new_from_date = CASE n WHEN 0  THEN f ELSE bp END,
new_to_date   = CASE n WHEN md THEN t ELSE ep END,rn
FROM d WHERE md >= n

Split date range (timestamp) into equal parts by Month- SQL Server Synapse

Experts,
Have a question regarding splitting a date range into equal parts by months including the time part

example – fromdate – 06/29/2020 09:00:00 and todate – 06/29/2021 09:00:00

Want to split this date range into twelve equal parts like below

06/29/2020 09:00:00 - 06/30/2020 12:59:59
07/01/2020 00:00:00 - 07/31/2020 12:59:59
.........
.......
06/01/2021 00:00:00 - 06/29/2021 09:00:00

I cant write recursive CTE as this is a sql synapse module I am running against..

With below query I am able to split the date part, but time part is not coming properly as above.. Please help me as this is a blocking my development

declare @FromTs DATETIME
declare @ToTs DATETIME
SET @FromTs = GetDate()
SET @ToTs = DATEADD(month, 12, @FromTs)

;WITH n(n) AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY (object_id))-1 FROM sys.all_columns
),
d(qi,qrt,qtt,n,f,t,md,bp,ep,rn) AS  
(
    SELECT 
    ,n.n, @FromTs, @ToTs, 
    DATEDIFF(MONTH, @FromTs, @ToTs),
    DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY( @FromTs),  @FromTs)),
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n, 
    DATEADD(DAY, 1-DAY( @FromTs),  @FromTs))))
    FROM n INNER JOIN <datetbl> AS d 
    ON @ToTs >= DATEADD(MONTH, n.n-1, @FromTs)
)
SELECT qi,qrt,qtt,
new_from_date = CASE n WHEN 0  THEN f ELSE bp END,
new_to_date   = CASE n WHEN md THEN t ELSE ep END,rn
FROM d WHERE md >= n

Could I cast a range spell as a melee attack? [closed]

I’m wanting to make a sorcerer that throws a little flare in his spell attacks. I was wondering if I could use a range spell attack as a melee attack like casting firebolt but punching with it instead of throwing it…

java – How to calculate FOV angle range based on facing and mouse aim position

The title is not that clear so I’ll explain. In my top down shooter game the player can be faced in any of the 8 directions using the keyboard. Then depending on the direction he will have a 90 degree field of view to turn based on the angle selected by mouse position. Obviously the mouse can go anywhere so it has to be restricted within -45 and 45 of the faced angle.

The eight direction angles:

    135 90 45
180             0
    225 270 305

So if he’s facing to the left at 180 his range will be -45 and 45 of that ie 135 to 225. So simply it’s mouseAngle - facingAngle which works fine except if he’s facing at around 0, 45 or 305 degrees where the mouse angle passes zero causing issues in calculation.

As it is I’m using this formula.

private void setAngleFOV() {
    if (facing == 0 || facing == 360)
        facing = mouseAngle < 360 && mouseAngle >= 180 ? 360 : 0;
    angleFOV = mouseAngle - facing;
    angleFOV = Math.max(Math.min(angleFOV, 45), -45);
}

It can either be 0 or 360 for either facing or mouseAngle, whichever works best but I haven’t found an ideal solution. The above formula works for facing right at angle 0, but not with diagonal angles 45 and 305.

It could maybe work with lots of if statements and such, but I feel there must be a better way?

statistics – Are odds even at head and tail for random range?

I’m not a math guru. So treat me like a layman and excuse my informal language, I’ll be as precise as I can be given my limited vocabulary.

I want to know whether or not the odds are even across an entire range of picks that, given a randomly selected sub range, any picked number will fall within that sub range.

More concretely, if I pick real numbers at random between 0 and 1 (floats), would all such numbers including those close to say the head and tail (0, 1 respectively) have the same odds of occurring within a randomly selected sub range of a particular size?

I suspect the answer is no, because if I picked a sub range size that was more than 50 percent of the real numbers, some numbers between zero and one would have a 100 percent chance of falling within that range every time while others would not. Would it work if I constrained the sub range size to less or equal to than fifty percent?

At the end of the day, I want to randomly and evenly select data points that are within a small proximity to each other and I happen to be working with floats that were randomly generated between 0 and 1.

Thanks, I hope my question isnt a disaster… even being pointed in the right direction for research would be helpful.