Calculation of Correlation

[Learner]

Hello,

I wanted to Calculate Correlation of my Table. My Data has 3 Columns , Party, Rate & Qty.  Which is shown below. I wanted to write an expression to calculate correlation with Rate & Qty. but for all Party separately and also for only Unique Rate i.e. if for A party 100 rate is used for 3 times than in correlation it mus take 100 once but take qty. as sum of all 3 times i.e. 157 MT in this case. 

Party	Rate	Qty.
A	100	55
B	200	45
C	300	40
A	200	48
B	300	46
C	100	42
A	100	51
B	300	44
C	200	39
A	300	42
B	100	44
C	200	39
A	100	51
B	200	44
C	300	39
A	200	51
B	300	44
C	200	39

If i make a straight table and calculate Correlation with Correl Function i.e. Correl(Qty.,Rate) i get -0.87885 for Party A 

which is calculated as follows:

Party	Rate	Qty.
A	100	55
A	200	48
A	100	51
A	300	42
A	100	51
A	200	51
		298
		-0.87885

But i wanted Correlation for to Consider Distinct Rate with sum of Qty. at Distinct rate. The Answer which i want is -0.999987. The Excel Sample is below :

Party	Rate	Qty.
A	100	157
A	200	99
A	300	42
		298
		-0.999987

I tried every thing but could not achieve this. Please help me with this.

Thanks & Regards,

Ashish

[Kushal_Chawda · Accepted solution]

try below expression with Part & Rate as a dimension

=Correl(total <Party>Rate,aggr(sum([Qty.]),Party,Rate))

[Learner]

Many Thanks Mr. Kush........ 

I just wanted to understand the formula..... Second part of Aggr i understood just wanted to understand the first part i.e. Total <Party> Rate

With regards

Ashish

[Brett_Bleess · Accepted solution]

Let's try this again, I found the following Help link that might be of some use to you:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Examples%20of%2...

Apologies if I missed the point of your question completely, but I am hopeful it may help a little.

Just realized there as a Design Blog post on Total too:

https://community.qlik.com/t5/Qlik-Design-Blog/What-does-the-TOTAL-qualifier-do/ba-p/1472990

Regards,
Brett