25 Replies Latest reply: Jan 9, 2015 12:39 PM by Sunny Talwar

# Showing Percentage Totals by Year

Hello,

Thank you for taking this question in advance.

Basically, I have data that spans across 4 years (2011, 2012, 2013, 2014). I want to show it by two Dimensions, 'Engaged' which is either a Yes or No, and over each year.

I want the percentages to show 'Y' or 'N' for 100% across EACH year, rather than 100% across all 4 years (which is how it currently shows).

Example: For 2011, I want 'Y' to show for 70% and 'N' to show for 30%, 2012 to show 'Y' for 80% and 'N' to show for 20%, rather than showing all the 'Y' and 'N' 2011 + 2012 + 2013+ 2014 adding up to 100%.

I have attached screen shots below of what the graph currently shows, along with the dimensions and expression.

I really appreciate the help!

Thanks!

• ###### Re: Showing Percentage Totals by Year

thanks

regards

Marco

• ###### Re: Showing Percentage Totals by Year

Hello,

Please find the sample data attached in the original post.

The attachments I added were 'Sample Data' and 'Year'.

I also have included the calculated field of Year Filed, which is just the year of the 'Last Filing Date' field within the Sample Data.

• ###### Re: Showing Percentage Totals by Year

hi Ryan,

i created a small data set for myself to get what your requirement is, you can achieve this by creating 2 expressions each enagaged as 'n' and engaged as 'y'. and dimenssion as year.

trigger:

id, Engaged, year, client name
1, n, 2014, abc
2, y, 2014, def
3, n, 2013, cfe
4, y, 2013, der
5, n, 2012, des
6, y, 2011, sas
7, y, 2014, qv
8, n, 2013, pyth
9, n, 2011, cpl
10, n, 2011, cog
]
;

the 2 expressions i used are:

=(

Count({<Engaged={'y'}>}DISTINCT id))/Count(distinct id)

=(

Count({<Engaged={'n'}>}DISTINCT id))/Count(distinct id)

• ###### Re: Showing Percentage Totals by Year

Hi!

Thank you, using the above expression worked! I would now like to take this a step further, and add a couple of other conditionals.

The current expressions I have now are the two below:

Count({<Engaged={'y'}>}DISTINCT id))/Count(distinct id)

Count({<Engaged={'n'}>}DISTINCT id))/Count(distinct id)

if( [IPO Type]='IPOs Filed & Priced',if([Expected / Actual Amount raised]>= 150000000

But am not sure how to incorporate this into my current expression. Is it an 'AND' statement? I am new to writing expressions in Qlik.

Thanks very much!

• ###### Re: Showing Percentage Totals by Year

Hi Saurabh,

I apologize for the double post, but I still cannot figure this out. If you, or anyone else, could respond to my post below it would be MUCH appreciated!!!

Thank you, using the above expression worked! I would now like to take this a step further, and add a couple of other conditionals.

The current expressions I have now are the two below:

Count({<Engaged={'y'}>}DISTINCT id))/Count(distinct id)

Count({<Engaged={'n'}>}DISTINCT id))/Count(distinct id)

if( [IPO Type]='IPOs Filed & Priced',if([Expected / Actual Amount raised]>= 150000000

But am not sure how to incorporate this into my current expression. Is it an 'AND' statement? I am new to writing expressions in Qlik.

Thanks very much!

• ###### Re: Showing Percentage Totals by Year

hi Ryan,

if you want to include the above condition you do not need to include an if condition, instead you can can expand your set analysis expression like:

=

Count({<Engaged={'y'},[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised]>={'150000000'}>}DISTINCT id)/Count(distinct id)

Regards

Saurabh

• ###### Re: Showing Percentage Totals by Year

and for engaged as no..

=

Count({<Engaged={'n'},[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised]>={'150000000'}>}DISTINCT id)/Count(distinct id)

• ###### Re: Showing Percentage Totals by Year

Hi Saurabh,

Thanks so much for your quick response. For some reason when I put that expression into Qlikview, I got the error:

'Error in set modifier expression'

I am not quite sure what this means. Would you be able to point me in the correct direction? The conditions that I have in the expression are correct, I am unsure of where I am going wrong.

Thanks again for all your help!

• ###### Re: Showing Percentage Totals by Year

See if this one works:

For Yes:

=Count({<Engaged={'y'},[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised] = {'>=150000000'}>}DISTINCT id)/Count(distinct id)

For No:

=Count({<Engaged={'n'},[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised] = {'>=150000000'}>}DISTINCT id)/Count(distinct id)

Best,

S

• ###### Re: Showing Percentage Totals by Year

Excellent! This worked! Thank you!

I almost have exactly what I am looking for with one exception:

**Please refer to attachment for reference**

The percentages are now showing for only the population that meets the criteria (i.e. in 2014, only 31% of the total population exceeded \$150M) so it is being split into only 9% and 22% respectively.

I was hoping to split the population up relative to each other (i.e. the entire population would be made up of only those that are greater than \$150M) so that it would show 100% total.

For example, looking at 2014: rather than showing only 9% Engaged and 22% Not Engaged (which takes into account the entire population), I would want it to show only the breakdown for the over \$150M population.

In effect, it would show Engaged:        29% (9/31)
Not Engaged:  71% (22/31)

I tried selecting the 'Relative' option in expressions and this did not work. Any idea on how I can get this to work?

Thanks again for all the help!

• ###### Re: Showing Percentage Totals by Year

Try this:

=Count({<Engaged={'y'},[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised] = {'>=150000000'}>}DISTINCT id)/Count({<[Expected / Actual Amount raised] = {'>=150000000'}>} DISTINCT id)

For No:

=Count({<Engaged={'n'},[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised] = {'>=150000000'}>}DISTINCT id)/Count({<[Expected / Actual Amount raised] = {'>=150000000'}>} DISTINCT id)

Instead of dividing with all the count, we are not dividing by the observations which are over 150000000.

Hope this helps

Best,

S

• ###### Re: Showing Percentage Totals by Year

And in case you have to account for the IPO Type in the denominator also then the formulas would be:

=Count({<Engaged={'y'},[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised] = {'>=150000000'}>}DISTINCT id)/Count({<[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised] = {'>=150000000'}>} DISTINCT id)

For No:

=Count({<Engaged={'n'},[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised] = {'>=150000000'}>}DISTINCT id)/Count({<[IPO Type]={'IPOs Filed & Priced'},[Expected / Actual Amount raised] = {'>=150000000'}>} DISTINCT id)

Best,

S

• ###### Re: Showing Percentage Totals by Year

Hi S,

That worked perfectly! Thank you!

I have one more question and then my Dashboard should be ready to go.

I am trying to show percentages of Accounts that have been Filed and Priced by Firm:

I have written four seperate expressions, one for each firm. See below:

For A

Count({<[Audit Firm]={'A'}>}DISTINCT ID)/Count(distinct ID)

For B

Count({<[Audit Firm]={'B'}>}DISTINCT ID)/Count(distinct ID)

Etc.

These are the 4 main Firms; however, there are many more small firms that make up the remainder of the 100% (Notice that the 4 stacked graphs currently do not add up to 100%).

I wanted to create a 5th expression called 'Other' that encompasses everything else that is not Firms A,B,C,D.

I would really appreciate if someone could help me formulate the proper expression.

• ###### Re: Showing Percentage Totals by Year

I am not sure if this is going the work the way you want, but if I had the data, I would write the following 5th expression:

=Count({<[Audit Firm]=e({<[Audit Firm] = {'A', 'B', 'C', 'D'}>})>}DISTINCT ID)/Count(distinct ID)

Here the e is telling you to exclude A, B, C, D from the numerator count.

Let me know if it works.

Best,

S

• ###### Re: Showing Percentage Totals by Year

When I tried using this expression I got the following error:

What does this signify?

Thank you

• ###### Re: Showing Percentage Totals by Year

Do you see any red curly line in the set modifier. Can you paste a snapshot of this particular expression?

• ###### Re: Showing Percentage Totals by Year

Yes,

Please see the screen-shot (apologies for the crossed out information, which is sensitive).

Please let me know if this helps

• ###### Re: Showing Percentage Totals by Year

I see you replaced e with Other. Try this formula as it is and let me know if it works or not:

=Count({<[Audit Firm]=e({<[Audit Firm] = {'A', 'B', 'C', 'D'}>})>}DISTINCT ID)/Count(distinct ID)

• ###### Re: Showing Percentage Totals by Year

hi Ryan,

you can create a new field in your script-----

if([Audit Firm]='X' or [Audit Firm]='Y' or [Audit Firm]='Z','Others',[Audit Firm])as new firm,

and use this in your 4 expressions you created for the 4 firms.

Regards

Saurabh

• ###### Re: Showing Percentage Totals by Year

Hi Saurabh,

Would this require me to list out every other firm as others? There are hundreds of them so ideally I would like to write and expression that simply amounts to:

If not Firm A, B, C, or D then 'Others'.

Do you know what the actual calculated field would have to be in order to create it within the script?

Thanks!

• ###### Re: Showing Percentage Totals by Year

if you have only 4 then you can write

if([Audit firm]='A','A',

if([Audit firm]='B','B',

if([Audit firm]='C','C',

if([Audit firm]='D','D',

'Others'

)))) as newfirm

• ###### Re: Showing Percentage Totals by Year

How about changing the expression to

count(DISTINCT  id) / count(TOTAL<[Year Filed]> id)

-Rob

• ###### Re: Showing Percentage Totals by Year

Hello Rob,

I'm trying to get the Denominator count by Year by using the AGGR function :

=Aggr(count(distinct ID),Last_Filing_year).

It shows the aggregated count correctly in one row of the year,while for the other row it shows as null.

Could you Please let me know what am I doing wrong here.

Thanks,

Kalyan

• ###### Re: Showing Percentage Totals by Year

You'll need to add the NODISTINCT qualifier to aggr().

By the way, I'd suggest that aggr() is the wrong function to use here. I would recommend TOTAL unless there is some other reason you need aggr(). (of course that's not what you asked )

count(TOTAL <Last_Filing_year> distinct ID)

-Rov

• ###### Re: Showing Percentage Totals by Year

Ok...Thank You very much Rob..:)