10 Replies Latest reply: Nov 29, 2012 9:14 PM by llxq_yoyo RSS

    set analysis with variable

      I want to use varaible in analysis

      for example

      SYear and SMonth are variables.

      The below expression work well

       

      count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

      UsageYear = {'$(=SYear)'},UsageMonth ={'$(=SMonth)'}>} distinct OrderId)

       

      But if I want to  use function on the varaible ,how should I write the set analysis?

       

      I mean if now I have a varaible named ReportDate,date type

      I want to use Year function to get the year part. like Year($(=ReportDate))

       

      then the analysis should be

       

      count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

      UsageYear = {'Year($(=ReportDate))'},UsageMonth ={'Month($(=ReportDate))'}>} distinct OrderId)

       

      I dont know how could i get it work?

        • Re: set analysis with variable

          hi llxq_yoyo

           

          try this $(ReportDate)

           

          like,

           

          count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'}, UsageYear = {'Year($(=$(ReportDate)))'}, UsageMonth = {'Month($(=$(ReportDate)))'}>} distinct OrderId)

           

          Regards,

          Akbar

          • Re: set analysis with variable

            try to insert dimension year or month before variable $(ReportDate)

             

            set this before :

             

            LET ReportDate = Periode;

             

            let's try :

             

            count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'}, UsageYear = {'Year($(=Year($(ReportDate))))'}, UsageMonth = {'Month($(=Month($(ReportDate))))'}>} distinct OrderId)

             

            Regards,

            Akbar


            • Re: set analysis with variable

              Try:

               

              count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

              UsageYear = {'=Year($(=ReportDate))'},UsageMonth ={'=Month($(=ReportDate))'}>} distinct OrderId)

                • Re: set analysis with variable

                  Wow ,Thank you ,awsome!

                  • Re: set analysis with variable

                    And another question,can you tell me the rules of set analysis. I don't understand why we should write like this. or do you have any references about writing set analysis which can guide me a correct direction?

                    • Re: set analysis with variable

                      Hey,i thought it worked,but actually,it just match the grammer,so i could run it.

                      but actually it didn't make any effects.

                      I mean if it worked .it just calculate this month's data,but it seems calculate all the months' data

                        • Re: set analysis with variable

                          Try:

                           

                          count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

                          UsageYear = {"=Year('$(=ReportDate)')"},UsageMonth ={"=Month('$(=ReportDate)')"}>} distinct OrderId)

                           

                          Note the quotes in set modifier selection.

                           

                          As for set analysis, you're actually dealing with three different concepts, set analysis, advanced search, and dollar sign expansion.

                           

                          Let's simplify your expression for illustration:

                           

                          count({<UsageYear = {"=Year('$(=ReportDate)')"}>} OrderId)

                           

                          This returns the number of orders in the full year specified by the ReportDate variable.

                           

                          Now let's take the parts:

                           

                          $(=ReportDate)

                           

                          This is the dollar sign expansion. It takes the contents of the variables and places in the expression. I suspect that $(ReportDate) will also work in your case; $(ReportDate) takes the contents of the variable while $(=ReportDate) evaluates ReportDate and places the result of the evaluation into the expresion.

                           

                          If ReportDate is set to 2012-11-29 then this produces the next part:

                           

                          "=Year('2012-11-29')"

                           

                          This is the advanced search (strings that start with =). You can use wildcards (*) or functions (e.g., sum, avg, year).

                           

                          The year fuction will evaluate to 2012 to produce:

                           

                          count({<UsageYear = {2012}>} OrderId)

                           

                          QlikView's Help has more information about set analysis.

                            • Re: set analysis with variable

                              Than you for so detailed description,but it seems not right.

                              And I've figured out the right way.

                              It should be below:

                              count(total<UsageYearMonth>{<UsageCategory = {'ActiveStudent'},

                              UsageYear = {$(=Year('$(ThisMonthEndDateVar)'))},

                              UsageMonth = {$(=Month('$(ThisMonthEndDateVar)'))}>} distinct OrderId)