Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Calculation of Correlation

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Learner

Contributor III

2020-07-20
07:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

623 Views

2 Solutions

Accepted Solutions

Kushal_Chawda

MVP

2020-07-20
08:43 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

try below expression with Part & Rate as a dimension

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

Brett_Bleess

Employee

2020-08-04
02:39 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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.

I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

505 Views

3 Replies

Kushal_Chawda

MVP

2020-07-20
08:43 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

try below expression with Part & Rate as a dimension

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

Learner

Contributor III

2020-07-20
11:44 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

579 Views

Brett_Bleess

Employee

2020-08-04
02:39 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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.

I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

506 Views