topic Re: Aggr/Aggr not producing expected results (possible formatting issue?) in App Development

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

t-gilchrist — Thu, 06 Dec 2018 13:22:33 GMT

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

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

avkeep01 — Thu, 06 Dec 2018 13:43:05 GMT

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')

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

sunny_talwar — Thu, 06 Dec 2018 13:52:15 GMT

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

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

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

avkeep01 — Thu, 06 Dec 2018 13:52:39 GMT

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.

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

t-gilchrist — Thu, 06 Dec 2018 14:03:40 GMT

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

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

t-gilchrist — Thu, 06 Dec 2018 14:05:24 GMT

@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 ?

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

avkeep01 — Thu, 06 Dec 2018 14:09:23 GMT

*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.

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

t-gilchrist — Thu, 06 Dec 2018 14:21:21 GMT

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?

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

sunny_talwar — Thu, 06 Dec 2018 14:26:48 GMT

@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?

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

sunny_talwar — Thu, 06 Dec 2018 14:30:34 GMT

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

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

t-gilchrist — Thu, 06 Dec 2018 14:50:21 GMT

Its becoming much more clear, I apologize for all the questions.

So my original thinking was if my result was correct as a number and matched my calculator using (30+(58/60)/71=30.96666 then that would be a verification that when i switch it back to duration that the number was correct.
But that was incorrect because 30.96666 is not the numerical representation of 00:30:58. In actuality I was dividing 0.02150/71 which is why I was getting a result of 0.0003 as a number.

So that would mean if I want to use duration as my final format then the original formula without *24*60 would be correct?

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

sunny_talwar — Thu, 06 Dec 2018 14:56:49 GMT

Absolutely bang on target 🙂