9 Replies Latest reply: Jun 28, 2017 1:25 PM by Jess K RSS

    Expression for IF more than and drill down

    Jess K

      Dear all

       

      I am new to Qlik and is struggling to understand and use codes and know where to place codes.

      Could someone please please help me with the two questions.

       

      I am working on a movie database that has columns called MovieTitle and Ratings.

      I need to show the following two items in my dashboard:

       

      1) List only movies that received at least 1000 ratings.

      2) Of those, list only movies that got ratings of 4.3 or higher.

       

      For (1), I have created a List Table and set my Dimension as MovieTitle. I am trying to set an Expression to calculate the total Ratings for each MovieTitle and get my List Table to display only MovieTitle that have a Rating score of 1000 and above. I am struggling to understand the IF statement and brackets, etc. How do I get my List Table to display this set of information?

       

      For (2), I need to use the results from the List Table in (1) above and further drill down to display only MovieTitle that received an AverageRating of 4.3 and above. I know how to calculate Average Rating using Avg (Rating) in my Expression.

       

      I will appreciate any help you can give.

       

      Many thanks

        • Re: Expression for IF more than and drill down
          Mark Ritter

          Can you provide examples?

           

          Is it possible to do your calculations in the script and then eliminate what you don't want to see in a Where clause?

           

          If not, it would be helpful to see what you are doing and an example of the real data.

          • Re: Expression for IF more than and drill down
            Alkis Michael

            Hi Jess,

             

            In your list table -> in expression -> you can write it as

            For 1) =if(Aggr(count(ratings),MovieName)>='1000',MovieName)

             

            For 2) you can further use same expression with and condition as follows,


            =if(Aggr(count(ratings),MovieName)>='1000' and Aggr(Avg(ratings),MovieName)>='4.3',MovieName)


            This will only display the desired movie names in list box or you can use same expression in any chart also.

            You just need to modify it accordingly.

            Hope this will help.

            Please, mark it as correct if it helps so that we can close this call.

             

            Thank you.

            Regards,

            Alkis

              • Re: Expression for IF more than and drill down
                Jess K

                Thank you all for responding so quickly.

                 

                Hi Alkis

                 

                I inserted the first code into my List Box Expression but weird data is displayed.

                I've extracted a small sample data from two different Excel files to give you an idea of the dataset used.

                Each User can rate more than one movie.

                Each movie can be rated by many Users.

                The MovieID is unique.

                 

                I need to sum the Rating of a movie to get the total ratings for that movie.

                My dashboard table should only display MovieGenres.Title (or movie titles) that have a total rating score of 1,000 and above.

                 

                From that data, I need to another table to list only movies that got ratings of 4.3 or higher.

                 

                For Ques 1)

                Here is my code used for a List Box under Expression.

                (Aggr(count(Rating),MovieGenres.Title)>='1000',MovieGenres.Title)

                 

                I also tried this:

                (Aggr(sum(Rating),MovieGenres.Title)>='1000',MovieGenres.Title)

                 

                I also tried a Pivot Table with Dimension as MovieGenres.Title and the Expression as above.

                I get a blank table too.

                 

                I'm not sure why it's showing be blanks or categories that are not relevant to the code.

                What am I doing wrong?

                 

                Many thanks

                 

                UserIDMovieIDRatingMovieGenres.Title
                111935Toy Story (1995)
                16613Jumanji (1995)
                19143Grumpier Old Men (1995)
                234084Waiting to Exhale (1995)
                223555Father of the Bride Part II (1995)
                211973Heat (1995)
                312875Sabrina (1995)
                328045Tom and Huck (1995)
                35944Sudden Death (1995)
                49194GoldenEye (1995)
                45955American PresidentThe (1995)
                59384Dracula: Dead and Loving It (1995)
                723984Balto (1995)
                729184Nixon (1995)
                710355Cutthroat Island (1995)
                827914Casino (1995)
                826873Sense and Sensibility (1995)
                920184Four Rooms (1995)
                1031055Ace Ventura: When Nature Calls
                  (1995)