Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

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

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Calculate decile/quartile/quintile etc when data i...

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

ljdlumley

Contributor III

2017-10-26
07:02 AM

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

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

MVP

2017-10-26
07:30 AM

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

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

7 Replies

sunny_talwar

MVP

2017-10-26
07:30 AM

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

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

2017-10-27
07:55 AM

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

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

2,940 Views

sunny_talwar

MVP

2017-10-27
07:59 AM

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

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

2,940 Views

bmesolutions

Partner - Contributor II

2017-10-27
08:04 AM

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

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

MVP

2017-10-27
08:06 AM

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

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

2,940 Views

ljdlumley

Contributor III

2017-10-30
06:54 AM

Author

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

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

2017-10-30
06:55 AM

Author

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

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

2,940 Views