topic Re: Problem with percentage calculation in Trellis charts in QlikView

Problem with percentage calculation in Trellis charts

Tue, 11 Mar 2014 13:08:50 GMT

Hey all

I have a problem with calculation percentage in Trelllis charts.

I want to show the enrollment of Bachelor- and Masterstudents in 8 different Universities in Denmark in a Trellis chart. I have no problem showing the actual number in my first Trellis Chart, but I also want a Trellis Chart where I have calculated the ration (in %)of all Bachelorstudents enrolled in each University divided by all enrolled Bachelorstudents in Total. The data ratio should sum to 100% in each of the Years in the Chart (2007-2013) . Likewise I want the same percentage for Masterstudents in the Chart (called "Kandidat" in Danish).

I use these dimensions/expression:

Dimensions:

University
Year
Type (Bachelor/Master)

Expression:

=sum({$<Status={'Samlet'}, Type={'dkuni, optag'}>} sum) / sum({$<Status={'Samlet'}, Type={'dkuni, optag'}>} TOTAL sum)

Listbox (where I select from Faculties - in the case in the Picture underneath it is "Humaniora" which 6 Universities have)

However my Trellis Charts looks like this:

Clearly the percentages are not calculated correctly - what am I doing wrong?

I have attached my data in excel-format

Hope somebody can help - thanx

Cheers

Bruno

Re: Problem with percentage calculation in Trellis charts

MK_QSL — Tue, 11 Mar 2014 13:33:05 GMT

Not clearly understood your question.

Can you let me know what could be the result you are expecting?

Re: Re: Problem with percentage calculation in Trellis charts

jagannalla — Tue, 11 Mar 2014 13:39:36 GMT

Hi,

Please find the attached.

Thanks,

Jagan

Re: Problem with percentage calculation in Trellis charts

Tue, 11 Mar 2014 13:55:48 GMT

Hi Jagan

Thanks for your answer, but it's not quite what I was looking for. I guess I was not specific enough in my original post - my fault, sorry

To be more specific:

The user have to choose one, and only one Faculty. But the expression calculates the percentage out of all 5 faculties together and not for each faculty as I wanted. (the selected Faculty should sum to 100 % if all Universities are summed for bachelorstudents and to 100% if all Universities for masterstudents summed)

Is this something you know how to do?

Thanks a lot

Cheers

Bruno

Re: Problem with percentage calculation in Trellis charts

jagannalla — Tue, 11 Mar 2014 14:14:29 GMT

Bruno,

Still we are confuse. Can you explain little bit more. If possible give output result what you are expecting.

Thanks,

Jagan

Re: Problem with percentage calculation in Trellis charts

Wed, 12 Mar 2014 08:17:38 GMT

Hi Jagan

I can try - here it goes.

I have only one listbox where the user can choose 1 (and only 1) of 5 faculties. Given by an example:

If I choose Faculty='Humaniora', the following Trellis Chart should show (Each single chart represents 1 of the 6 Universities that have the Faculty 'Humaniora'):

Mark the for each Year AND each Type (Bachelor/Kandidat) the cumulutative percent for all Universities adds up to 100%. If I choose another Faculty (for example 'Samfundsvidenskab'), the new Trellis chart would look like this:

Again the cumulutative percentage for each Year and each Type adds up to 100 %. Thats what I want my Trellis Chart to do.

I hope this makes it clearer, and that you have a solution

Thanks in advance

Cheers

Bruno

Re: Re: Problem with percentage calculation in Trellis charts

jagannalla — Wed, 12 Mar 2014 09:19:37 GMT

Hi,

Please find the attached file.

I had calculated like this,

Bachelor Exp:

100*(Sum of Bachelor value for each university and year / Sum of total Bachelor value for all university and years(i.e. total value for all university and all years) )

- In numbers tab I had checked Show percent. It will calculate the exp with 100

- Based on selection in faculty the expression will change in Numerator and denominator.

If you select Humaniora, it gives the result based on Humaniora.

If you don't want to change denominator based on your current selection in app, use {1}. If you want to remove Faculty selection in denominator, use {<Faculty =>}

Thanks,

Jagan

Re: Problem with percentage calculation in Trellis charts

MK_QSL — Wed, 12 Mar 2014 09:39:12 GMT

Like This ?

Update :

Change your expressions as below..

for Bachelor

=Sum({<Degree={'Bachelor'}>}Val)/Sum(TOTAL <Year> {<Degree={'Bachelor'}>}Val)

for Kandidat

=Sum({<Degree={'Kandidat'}>}Val)/Sum(TOTAL <Year> {<Degree={'Kandidat'}>}Val)

Re: Problem with percentage calculation in Trellis charts

jagannalla — Wed, 12 Mar 2014 09:49:45 GMT

Bruno- You can use Manish expression if you need total years value in each trills chart.

Re: Problem with percentage calculation in Trellis charts

Wed, 12 Mar 2014 10:06:42 GMT

Hi Jagan

Thanks for your answers, but I'm sorry to tell you that I'ts still not what I want the Trellis to show.

From my first example Picture above for Faculty='Humanities and Type='Kandidat', you can see that the values for the 6 universities in 2013 cumulates to 100% (KU=28%, AU=22%, AAU=12%, SDU=22%, CBS=7%, RUC=9%).

I want the chart to cumulate to 100% each year.

In your answer the percentages are to low. Maybe it's because the expression cumulates for all Years and not for each Year seperately.

Maybe the answer is a Manish expression, but I don't know what it is. I tried to google it, but didn't get anything. Do you know where I can find information on this?

Again thanks for all your help so far

Thanks

Bruno

Re: Problem with percentage calculation in Trellis charts

Wed, 12 Mar 2014 10:12:19 GMT

By bad - I didn't see Manish answer and thought that there was something called a "Manish Expression" in Qlikview (guess WHO's feeling like a complete fool now )

Thanks a lot both of you - You solved my problem!

Cheers

Bruno

Re: Problem with percentage calculation in Trellis charts

MK_QSL — Wed, 12 Mar 2014 10:19:56 GMT

My pleasure ...!!