Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

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

Hello all,

I have a similar table in my data load:

Product NumberMPGAB
111aaaaA5212.2
222bbbB6536
3c3c3cA55631
As34s439T5412

ADSWRW22

B643212
FWE544SD9T21456
ADS540-FDA5325

 

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)

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Hi,

Your expression should be 

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

and the dimension is MPG

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

7 Replies
MVP & Luminary
MVP & Luminary

Hi,

Your expression should be 

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

and the dimension is MPG

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Creator
Creator

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

MVP & Luminary
MVP & Luminary

HI,

Can you explain with some example and expected output.

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Creator
Creator

Hi,

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

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

Creator
Creator

Hi,

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

FIGURE1:

Capture.PNG

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

MVP & Luminary
MVP & Luminary

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

Creator
Creator

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