Aggr/Aggr not producing expected results (possible formatting issue?)

[t-gilchrist]

Hello all,

I have a data set of downtime instances that looks similar to this...

Order#     MaterialID     TotalBatchesInOrder     DowntimeDuration(hh:mm:ss)
2774         Tstock             23                                           00:06:52
2847         Tstock             25                                           00:08:27
2847         Tstock             25                                           00:05:16
2847         Tstock             25                                           00:03:56
2896         Tstock             23                                           00:06:27
2866         Astock            25                                           00:05:28

The Goal is to make an expression on the measure of a bar chart to sum the total downtime duration for a given MaterialID and divide it by the total batches ran for the corresponding MaterialID, excluding instances of a repeating Order# to get the downtime per batch as a measure with MaterialID as the dimension on the chart.

My current expression is...
(Aggr(Sum(DowntimeDuration),Material))/(Sum(Aggr(Max(TotalBatchesInOrder),Material,Order#)))

Expression Testing
- When I use only Aggr(Sum(DowntimeDuration),Material) and filtered to "Tstock" I get my desired result of 00:30:58 when I format my bar chart measure as duration.
- When I use only Sum(Aggr(Max(TotalBatchesInOrder),Material,Order#)) and filtered to "Tstock" I get my desired result of 71 batches when i format my bar chart measure as a number.

Results
But when I use the full expression I do not get my expect output of 0.4362 (30.9666/71) instead...
- When the chart is formatted as a number I get 00.00030
- When the chart is formatted as a duration I get 00:00:26

Any help in figuring out why I am not getting my expected output or alternate ways to achieve my goal would be appreciated 

[avkeep01]

Hi @t-gilchrist

Have you tried adding a sum over the first aggregation? 

INTERVAL(SUM(Aggr(Sum([DowntimeDuration(hh:mm:ss)]),MaterialID))
/
(Sum(Aggr(Max(TotalBatchesInOrder),MaterialID,Order#))),'hh:mm:ss')

[sunny_talwar · Accepted solution]

I think all your read need is to multiply by 24 * 60

(Aggr(Sum(DowntimeDuration),Material))/(Sum(Aggr(Max(TotalBatchesInOrder),Material,Order#)))
* 24 * 60

[avkeep01]

This is the result I get: 

MaterialID	A/B	A	B
	0:00:23	0:36:26	96
Astock	0:00:13	0:05:28	25
Tstock	0:00:26	0:30:58	71

A = SUM(Aggr(Sum([DowntimeDuration(hh:mm:ss)]),MaterialID))
B = Sum(Aggr(Max(TotalBatchesInOrder),MaterialID,Order#))
A/B = SUM(Aggr(Sum([DowntimeDuration(hh:mm:ss)]),MaterialID)) /Sum(Aggr(Max(TotalBatchesInOrder),MaterialID,Order#))

I only used MaterialID as dimension. 

[t-gilchrist]

Thanks for the repy @avkeep01
I had not yet tried your suggestion but doing so did not change the result for "Tstock" in either format.The result of 00:00:26 you got for "Tstock" A/B is the same as in the expression without Interval(Sum. 

It looks as though StalWar1's answer produces the right result when as a number

[t-gilchrist]

@sunny_talwar Thanks for your reply

Your suggestion has solved my issue.

Could you explain why I have to multiply my previous result by * 24 * 60 ?

[avkeep01]

*24 is time 24 hours
and * 60 is times 60 minutes. 

That is because 1  = 24 hours (1 day), and to get from hours to minutes you'll need to divide by 60. For seconds you'll need to divide by 60 again. 

For example: 

0.5 days * 24 = 12 hours * 60 = 720 minutes. 

[t-gilchrist]

I understand the math behind units of time

I guess what I'm trying to understand is why I needed to do it...
- Aggr(Sum(DowntimeDuration),Material)) was giving me 00:30:58 and (Sum(Aggr(Max(TotalBatchesInOrder),Material,Order#))) resulted in 71 so I would have expected my result to already be correctly outputed with out conversion

So if I would define DowntimeDuration as only mm:ss would I only need to multiply by 60?

[sunny_talwar]

---

@t-gilchrist wrote:

- Aggr(Sum(DowntimeDuration),Material)) was giving me 00:30:58

---

This is a Time formatting of the number... but if you convert this into Number... you will see that this is a very small number. Basically... 1 days is considered to number 1.... and 1 hour  = 1/24 and 1 minute = 1/(24*60). So what will be 30 minutes and 58 seconds? So, that is why multiple by 24 * 60 to get the correct numerical representation.

Does that make sense?

[sunny_talwar]

This is what 00:30:58 looks as a number