16 Replies Latest reply: Jul 13, 2017 8:51 AM by Michael Solomovich RSS

    Sum if with a set year

    Reiko Hayami

      Hi - I have the below expression that is working for a survey results.  The table shows results as below.

       

      100*Sum(IF([Would Recommend]='Yes',1,0))/Sum(IF([Would Recommend]='Yes' or [Would Recommend]='No',1,0))

       

        I want to show only 2016FY and 2017YTD without users having to select years. The year is called "Completed Year".  Ideally, I want to have the table to look like this.  But I don't think it's possible.   So if you could tell me how I can get to the expression to show just the 2016 without selecting any year filter, that would be a great start for me.

       

       

      I tried this but of course it doesn't work..

      100*Sum({1}IF([Completed Year]='2016' and [Would Recommend]='Yes',1,0))/Sum(IF([[Completed Year]='2016' and [Would Recommend]='Yes' or [Would Recommend]='No',1,0))

       

      Thank you!

        • Re: Sum if with a set year
          Sinan Ozdemir

          Hi,

           

          Would you be able to attach a sample app or data set?

           

          Thanks

          • Re: Sum if with a set year
            Michael Solomovich

            (I've made some assumptions about your data, so if it doesn't work, please attach your app.)

             

            First, you can simplify the original expression:

             

            100*count({<[Would Recommend]={'Yes'}>} [Would Recommend])/count([Would Recommend])

             

            Next, for the year 2016:

             

            100*count({<[Would Recommend]={'Yes'}, [Completed Year]={2016}>} [Would Recommend])/count({<[Completed Year]={2016}>} [Would Recommend])

             

            I hope you can figure out the rest...

              • Re: Sum if with a set year
                Reiko Hayami

                Thank you for this!  The expression itself works perfectly.  Now my table looks like this, mind you, I need to do 2016 and 2017 for other categories as well but I can probably apply the same to them. Problem here is I need to have the Year as columns and Recommend in row, but I can't figure out how to make the visualization work that way. :[

                 

                  • Re: Sum if with a set year
                    Michael Solomovich

                    Here is an example of a table without the real dimensions.  Actually, there is a dimension there, based on ValueList() function.

                    Of course it is not a solution but an idea how you can build your table.  I'm just using dummy expressions here,

                      • Re: Sum if with a set year
                        Reiko Hayami

                        I actually don't know how to open the qvf file.  Could you please direct me to a thread that may have instructions on how to view that?  Thank you.

                          • Re: Sum if with a set year
                            Michael Solomovich

                            Well, you're on Sense forums, and your screenshots look "sensy" ,that is qvf.

                            Anyway...  First, you download the file.  Next:

                            If you have Qlik Sense Desktop, copy the file into C:\Users\<actual user, that is yourself>\Documents\Qlik\Sense\Apps
                            If you use Qlik Server, you have to go Applications, and import the file.

                              • Re: Sum if with a set year
                                Reiko Hayami

                                I don't have Qlik Sense Desktop, so when I try to import the saved qvf file from the app, it says the file format is not supported, looks like only qvd and qvx..

                                  • Re: Sum if with a set year
                                    Michael Solomovich

                                    QMC certainly can import qvf files.  Maybe it is a version difference (?)  I saved it now in an older version, see if you can import it now.

                                      • Re: Sum if with a set year
                                        Reiko Hayami

                                        It still says the file is not supported;;  I will ask another staff here in a bit to see how they are importing.  I used Region instead of Year for now, which should be something the management would want to see anyways.

                                         

                                        Meanwhile I lied and could not get the other ones to work... Here is the working expression for NPS numbers, and how can I make this one so that it would be for Completed Year 2016?  Thank you!!

                                         

                                        (SUM(if([Would Recommend]='Yes',1,0))/COUNT([Would Recommend]) - SUM(if([Would Recommend]='No',1,0))/COUNT([Would Recommend]))*100

                                          • Re: Sum if with a set year
                                            Reiko Hayami

                                            Actually I did the following for NPS.... It spits out a reasonable number... Is this correct or can it be simplified?  Thanks..

                                             

                                            100*count({<[Would Recommend]={'Yes'}, [Completed Year]={2016}>} [Would Recommend])/count({<[Completed Year]={2016}>} [Would Recommend])-(100*count({<[Would Recommend]={'No'}, [Completed Year]={2016}>} [Would Recommend])/count({<[Completed Year]={2016}>} [Would Recommend]))

                                              • Re: Sum if with a set year
                                                Michael Solomovich

                                                Looks fine to me.  It could be a little bit simplified, not much.  E.g., as if you have

                                                 

                                                100*A/C - 100*B/C

                                                can be replaced with

                                                100(A-B)/C

                                                 

                                                But it's fine to keep as is, of course.

                                                 

                                                And, since you have a dimension now in your table (Region), you don't need my qvf anymore.  It is specifically for the "no-dimension" situation.

                                                  • Re: Sum if with a set year
                                                    Reiko Hayami

                                                    Michael - I got everything else to work including var. and background color expressions, BUT, the very 1st expression for Recommend actually doesn't give the correct #.  The result of the original expression I had below for 2016 would have been 94.7.  They are always in the 90's.

                                                     

                                                    100*Sum(IF([Would Recommend]='Yes',1,0))/Sum(IF([Would Recommend]='Yes' or [Would Recommend]='No',1,0))

                                                     

                                                    The expression you did with 2016 below gives 89.5.

                                                     

                                                    100*count({<[Would Recommend]={'Yes'}, [Completed Year]={2016}>} [Would Recommend])/count({<[Completed Year]={2016}>} [Would Recommend])

                                                     

                                                    I'm really not sure what could be causing this because I did all other categories in the way you did and they all give correct #s. Just this one. Please take another look? ;_;  Thank you so much.

                                                      • Re: Sum if with a set year
                                                        Michael Solomovich

                                                        As I've said at the beginning, there maybe something about you data that I can't guess.  For example, there could be [Would Recommend] values other than 'Yes' and 'No'.  If this is the case, this will help:


                                                        100*count({<[Would Recommend]={'Yes'}, [Completed Year]={2016}>} [Would Recommend])/count({< [Would Recommend]={'Yes','No'}, [Completed Year]={2016}>} [Would Recommend])


                                                        If this doesn't help, I'm out of guesses.  You can either upload your app to help me to find the reason, or reverse back to sum(), with some modifications:

                                                         

                                                        100*Sum({<[Completed Year]={2016}>} IF([Would Recommend]='Yes',1,0))/Sum({<[Completed Year]={2016}>} IF([Would Recommend]='Yes' or [Would Recommend]='No',1,0))