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: 
Learner
Contributor III
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. 

PartyRateQty.
A10055
B20045
C30040
A20048
B30046
C10042
A10051
B30044
C20039
A30042
B10044
C20039
A10051
B20044
C30039
A20051
B30044
C20039

 

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:

PartyRateQty.
A10055
A20048
A10051
A30042
A10051
A20051
  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 :

PartyRateQty.
A100157
A20099
A30042
  298
   
  -0.999987

 

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

Thanks & Regards,

Ashish

 

 

Labels (1)
2 Solutions

Accepted Solutions
Kushal_Chawda

try below expression with Part & Rate as a dimension

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

 

Annotation 2020-07-20 134233.png

View solution in original post

Brett_Bleess
Employee
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.

View solution in original post

3 Replies
Kushal_Chawda

try below expression with Part & Rate as a dimension

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

 

Annotation 2020-07-20 134233.png

Learner
Contributor III
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

Brett_Bleess
Employee
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.