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

    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
          Marco Wedel

          please provide sample data

           

          thanks

           

          regards

           

          Marco

          • Re: Showing Percentage Totals by Year
            saurabh singh

            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:

             

            LOAD * INLINE [
            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)

                 

                 

                I would like to add:

                 

                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)

                   

                   

                  I would like to add:

                   

                  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
                      saurabh singh

                      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
                          saurabh singh

                          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
                                Sunny Talwar

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

                                    QV Attach.JPG

                                     

                                    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
                                        Sunny Talwar

                                        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
                                            Sunny Talwar

                                            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
                                Rob Wunderlich

                                How about changing the expression to

                                 

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

                                 

                                -Rob