Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ljdlumley
Contributor III
Contributor III

Calculate decile/quartile/quintile etc when data is aggregated.

Hello,


I've looked through the help and Community pages but can't find a solution to my issue.


I'm attempting to find the decile position of a Course of the average Mark for students on a Course.

Using dummy data with just one value per course it works absolutely perfectly with the following;

if(Mark<=Fractile(TOTAL Mark,0.1),1,

if(Mark<=Fractile(TOTAL Mark,0.2),2,

if(Mark<=Fractile(TOTAL Mark,0.3),3,

if(Mark<=Fractile(TOTAL Mark,0.4),4,

if(Mark<=Fractile(TOTAL Mark,0.5),5,

if(Mark<=Fractile(TOTAL Mark,0.6),6,

if(Mark<=Fractile(TOTAL Mark,0.7),7,

if(Mark<=Fractile(TOTAL Mark,0.8),8,

if(Mark<=Fractile(TOTAL Mark,0.9),9,10)))))))))

In my real data the expression needs to be Avg(Mark) to calculate the average for all students on the course

if(Avg(Mark)<=Fractile(TOTAL Avg(Mark),0.1),1,

if(Avg(Mark)<=Fractile(TOTAL Avg(Mark),0.2),2,

if(Avg(Mark)<=Fractile(TOTAL Avg(Mark),0.3),3,

if(Avg(Mark)<=Fractile(TOTAL Avg(Mark),0.4),4,

if(Avg(Mark)<=Fractile(TOTAL Avg(Mark),0.5),5,

if(Avg(Mark)<=Fractile(TOTAL Avg(Mark),0.6),6,

if(Avg(Mark)<=Fractile(TOTAL Avg(Mark),0.7),7,

if(Avg(Mark)<=Fractile(TOTAL Avg(Mark),0.8),8,

if(Avg(Mark)<=Fractile(TOTAL Avg(Mark),0.9),9,10)))))))))


This gives every Course a value of 10, the final False value above. My thinking so far is that I need to add in Aggr somewhere but everything I've tried has failed.


Any suggestions would be very gratefully received.


Thanks in advance


John



1 Solution

Accepted Solutions
sunny_talwar

May be this

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.1),1,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.2),2,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.3),3,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.4),4,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.5),5,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.6),6,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.7),7,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.8),8,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.9),9,10)))))))))

Also, are you using this as an expression or a dimension? If this is an dimension, then try this

Aggr(

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.1),1,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.2),2,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.3),3,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.4),4,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.5),5,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.6),6,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.7),7,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.8),8,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.9),9,10)))))))))

, Student)

View solution in original post

7 Replies
sunny_talwar

May be this

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.1),1,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.2),2,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.3),3,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.4),4,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.5),5,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.6),6,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.7),7,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.8),8,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.9),9,10)))))))))

Also, are you using this as an expression or a dimension? If this is an dimension, then try this

Aggr(

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.1),1,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.2),2,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.3),3,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.4),4,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.5),5,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.6),6,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.7),7,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.8),8,

if(Avg(Mark) <= Fractile(TOTAL Aggr(Avg(Mark), Student), 0.9),9,10)))))))))

, Student)

bmesolutions
Partner - Contributor II
Partner - Contributor II

Hi John using Avg(Total Marks, Course) should help get you towards it I would have thought

or in the script

LOAD *,

if(Mark<=Fractile(TOTAL Mark,0.1),1,

if(Mark<=Fractile(TOTAL Mark,0.2),2,

if(Mark<=Fractile(TOTAL Mark,0.3),3,

if(Mark<=Fractile(TOTAL Mark,0.4),4,

if(Mark<=Fractile(TOTAL Mark,0.5),5,

if(Mark<=Fractile(TOTAL Mark,0.6),6,

if(Mark<=Fractile(TOTAL Mark,0.7),7,

if(Mark<=Fractile(TOTAL Mark,0.8),8,

if(Mark<=Fractile(TOTAL Mark,0.9),9,10)))))))));

Load Course, Avg(Mark) as Mark

Resident Data Group by Course

sunny_talwar

Preceding load will not work as you are using another aggregation (Fractile) without a group by. Also, I don't think there is such a thing as TOTAL in the script

bmesolutions
Partner - Contributor II
Partner - Contributor II

Sorry TOTAL Mark won't work in the script,  but that could be calculated and placed into a variable (or each Fractile could be placed into its own variable then referenced in the script

sunny_talwar

Its going to be slightly more complicated then you are portraying, but it depends on what OP wants to do here

ljdlumley
Contributor III
Contributor III
Author

Thanks Sunny, I was using it in an expression so went with the first part. It didn't work aggregating at student level but when I substituted in Course it gave me the results I was expecting.

John

ljdlumley
Contributor III
Contributor III
Author

Thanks Dave, hopefully this is just a one off so am going with what seems to be the simpler option. I might need to come back to you sometime in the future about adding expressions into variables!

John