9 Replies Latest reply: Nov 18, 2009 10:01 PM by John Witherspoon RSS

    Set Analysis question

    grahamwarwick

      I am very novice to this and I think set analysis will do what i need.

      I am using the following expression;

       

      Count({<[Policy Status] = {'Cancelled'}>} [PolicyID])

      Now this works fine for a simple count, however in the table i have 2 seperate date fields, one is a cancellation date and the other is a quoted date, if i just choose the cancellation month it gives the right data, if i also choose a quotation month then it alters the cancellation figure, i want to show the values independantly and not have one interfere with the other as it usually does with any linked tables.

      I hope this makes sense

      thanks in advance

      Graham



        • Set Analysis question

          In this case you should do something like

          Count({1 <[Policy Status] = {'Cancelled'}>} [PolicyID])

          Sébastien

          • Set Analysis question
            Shumail Hussain

            Hi Graham,

            Did u try using $ set analysis? may it works.

             


            Count({$<[Policy Status] = {'Cancelled'}>} [PolicyID])


             

            Regards,
            Shumail Hussain

              • Set Analysis question
                grahamwarwick

                I have tried both $ and 1, the problem comes that if i choose 1 then every cancellation ever shows, if i choose $ then just the selected year works, but when using $ if i choose another input field then it alters this field.

                  • Set Analysis question
                    Neil Miller

                    In order to ignore the Quotation Month selection, try this:

                    Count({<[Policy Status] = {'Cancelled'}, [Quotation Month]=>} [PolicyID])


                      • Set Analysis question
                        grahamwarwick

                        Thats great, thanks so much for the help it works a treat.

                        Now i have this problem;

                         

                         

                         

                         

                         

                        Count({<ActivityEffectedYear = {2009}>} [Beneficiary Status] = 'Cancelled',[BeneficiaryID])



                         

                         

                        Hopefully you can see what i am trying to do, i want to count the beneficiaryID when the beneficiary status is Cancelled and the activity effected year is > 2009. I think i have missed something obvious.

                        Thanks.





                         

                          • Set Analysis question
                            Shumail Hussain

                            Try this,

                             


                            count({$<ActivityEffectedYear= {">2009"}, [Beneficiary Status] = {"Cancelled"}>} [BeneficiaryID])


                            Regards,
                            Shumail Hussain

                              • Set Analysis question
                                grahamwarwick

                                Shumail that worked thanks, although had to change the >2009 to 2009.

                                I have just realised that i also need to add in ActivityText = 'CANCEL INSTRUCTION'

                                Where would i add that in?

                                  • Set Analysis question
                                    John Witherspoon

                                     


                                    grahamwarwick wrote:Shumail that worked thanks, although had to change the >2009 to 2009.
                                    I have just realised that i also need to add in ActivityText = 'CANCEL INSTRUCTION'
                                    Where would i add that in?




                                    count({<[ActivityEffectedYear] = {'$(=year(today()))'}
                                    ,[Beneficiary Status] = {'Cancelled'}
                                    ,[ActivityText] = {'Cancel Instruction'}
                                    >} [BeneficiaryID])

                                    You specified that you wanted >2009, so that's why Shumail did it that way. In any case, I wouldn't hard code values like 2009 into a real application unless you REALLY want 2009, no matter what year it currently is. I suspect that what you really want is the current year, whatever that year might be. The above should handle that.

                                    I also I changed the double quotes to single quotes in the Beneficiary Status. You'll get the same result either way, but double quotes specify a search string. So you would have QlikView first search for all Beneficiary Statuses matching the search string "Cancelled". Only one can match, so it will find it and select it. Single quotes specify a literal, which SHOULD (untested, and based soley on basic design experience) short cut this process and therefore execute more quickly. In practice, I can't image how you would ever detect a difference. Still, I think it's helpful to understand the difference between literals and search strings, and to be explicit in the code about which is which.

                          • Set Analysis question
                            John Witherspoon

                             


                            Shumail Hussain wrote:
                            Hi Graham,
                            Did u try using $ set analysis? may it works.
                            <blockquote>
                            Count({$<[Policy Status] = {'Cancelled'}>} [PolicyID]) </blockquote>

                            Regards,
                            Shumail Hussain<div></div>
                            Just a quick note - the $ is optional in this expression, and is the default behavior. So this expression will behave exactly the same and return exactly the same result with or without the $.