Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try below expression with Part & Rate as a dimension
=Correl(total <Party>Rate,aggr(sum([Qty.]),Party,Rate))
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
try below expression with Part & Rate as a dimension
=Correl(total <Party>Rate,aggr(sum([Qty.]),Party,Rate))
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
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