Skip to main content

# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## Calculation of Correlation

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

Labels (1)
• ### Correlation With Repeat Price

2 Solutions

Accepted Solutions

try below expression with Part & Rate as a dimension

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

Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
3 Replies

try below expression with Part & Rate as a dimension

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

Contributor III
Author

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

Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Community Browser