topic Re: Summing up Like terms in a field in App Development

Summing up Like terms in a field and finding individual avg based on the like terms

waleeed_mahmood — Sat, 16 Nov 2024 18:53:26 GMT

Hello all,

I have a similar table in my data load:

Product Number	MPG	A	B
111aaaa	A5	21	2.2
222bbb	B6	53	6
3c3c3c	A5	56	31
As34s43	9T	54	12
ADSWRW22	B6	432	12
FWE544SD	9T	214	56
ADS540-FD	A5	32	5

So what i want to do is collect like terms in the MPG column and (A-B)/B for each distinct MPG. After finding the Change, i would also like to find individual avg of EACH MPG. so basically divide the change by the number of times an MPG occurs in the original table.

Resulting Table: (NOTE: Change and AVG(Change) columns don't have to be separated as long as the avg is found)

MPG	Change	Individual AVG(Change)
A5	(21-2.2)/2.2 + (56-31)/31	((21-2.2)/2.2 + (56-31)/31 + (32-5)/5)/3
B6	(53-6)/6 + (432-12)/12	((53-6)/6 + (432-12)/12)/2
9T	(54-12)/12 + (214-56)/56	((54-12)/12 + (214-56)/56)/2

You help is appreciated.

Please let me know if you require any more information.

Thank you.

Re: Summing up Like terms in a field

kaushiknsolanki — Tue, 24 Mar 2020 19:54:34 GMT

Hi,

Your expression should be

Sum(Aggr((Sum(A)-Sum(B))/Sum(B),[Product Number],MPG))

and the dimension is MPG

Re: Summing up Like terms in a field

waleeed_mahmood — Tue, 24 Mar 2020 22:04:56 GMT

Hi,

is it possible to divide it by the number of MPG occurrences after?

So for example, all the values will be divided by 2 in the table above since each MPG appears twice. Basically like finding AVG after the sum.

Thank you

Re: Summing up Like terms in a field

kaushiknsolanki — Wed, 25 Mar 2020 06:05:11 GMT

HI,

Can you explain with some example and expected output.

Re: Summing up Like terms in a field

waleeed_mahmood — Wed, 25 Mar 2020 14:07:00 GMT

Hi,

For sure, i can expand on my question. So, the resulting table would be something similar to the following table:

MPG	Change
A5	((21-2.2)/2.2 + (56-31)/31)/2
B6	((53-6)/6 + (432-12)/12)/2
9T	((54-12)/12 + (214-56)/56)/2

so basically, after the expression you wrote above, i would like to divide it by the number of time a MPG occurs. So, in the table above, A5 appears twice and so does the other two MPGs.

Re: Summing up Like terms in a field

waleeed_mahmood — Fri, 27 Mar 2020 15:18:54 GMT

Hi,

the expression you gave in your reply, sums everything up for one MPG value. it doesnt put them in their respective place.

FIGURE1:

my chart expression is as follows:

Sum(Aggr((Sum(FSIML2NP)-Sum("C L2 NP"))/Sum("C L2 NP"),[oM_PN],"F MPG"))

does the product number in your expression have to be uniqe? what is the use of?

Thank you

Re: Summing up Like terms in a field

rwunderlich — Fri, 27 Mar 2020 22:26:40 GMT

Where did "Code Value" come from? It's not in the original question.

If the data is as shown in your original post and the dimension is MPG, I think the correct expression is:

Sum((A-B)/B) / Count([Product Number])

-Rob

Re: Summing up Like terms in a field

waleeed_mahmood — Tue, 31 Mar 2020 03:06:46 GMT

Hi Rob,

"Code Value" is MPG in the original post. Also, im not sure why would you divide it by Count[Product Number]. The reason i have /2 in one of my replies is because each MPG appears twice in the original table. In my actual dataset, one MPG value could appear 200 times.

your expression would just divide it by the total numbers of product numbers. my problem requires diving it by the number of time EACH MPG occurs in a table.

Please let me know if you require more information.

Thank you