Beast Mode to Calculate Gains in Values

domo_arigato

Hello, 

 

I'm trying to create a beast mode to calculate gains in values over time to be displayed on a single value summary card. We have both the values and their corresponding dates. We are trying to calculate the gain based on the difference between the lowest and highest value (given that the highest occurs after the lowest) in a date range. 

For example: 
Date     Value

5/20      2.2

5/21      2.1

5/22      2.2

5/23      2.3

5/24      2.7

 

With this data, we would return a value of 0.6, because the difference between the highest value (2.7) and lowest value (2.1) is 0.6. The highest value also occured after the lowest value. 

Because we are taking into account that the highest must occur after the lowest, we cannot do a simple max(value) - min(value) that would omit certain edge cases. 

For example, if we filter our dates, here is a case where we do not see a gain over time: 

 

Date     Value

5/20      2.2

5/21      2.1

 

So having our beast mode as max(value) - min(value) would calculate 0.1, and our summary number card would incorrectly show this as a gain when in fact there wasn't, due to the values decreasing over time. We might want to report that there was no gain or -0.1 gain on our summary number card. 

 

Date     Value

5/20      2.2

5/21      2.1

5/22      2.2

 

In this last edge case, we will add another date to our range. Based on our definition, we do see a gain over time of 0.1 where the highest value occurs after our lowest value (from 5/21 to 5/22). So it should correctly display 0.1, but also take into account that this it needs to compare the highest value from the most recent date (5/22 as opposed to 5/21, which was in our last example), so that it can correctly report a gain. 

Any hints on how to do this? Trying to sort this out ASAP, and would greatly appreciate the help!

 

 

 

 

 

 

 

ST_Superman

I have one more question:

 

What output would you expect to see in this scenario:

Date     Value

5/20      2.2

5/21      2.1

5/22      2.2

5/23      2.3

5/24      2.7

5/25      2.0

 

My assumption would be a null value?  When there is no growth after the minimum value in a timeperiod, do you want an output of zero or null?

Unknown

---

@ST_-Superman-_ wrote:

I have one more question:

 

What output would you expect to see in this scenario:

Date     Value

5/20      2.2

5/21      2.1

5/22      2.2

5/23      2.3

5/24      2.7

5/25      2.0

 

My assumption would be a null value?  When there is no growth after the minimum value in a timeperiod, do you want an output of zero or null?

---

Scott - that is an excellent point to bring up! I brought this up when you posted this and am still waiting on internal confirmation of what I should expect to return for this edge case. I may repost when I have the answer, for visibility!