6 Replies Latest reply: Sep 28, 2014 3:36 AM by Massimo Grossi RSS

    Set analysis question

      Hi All,

       

      I have here a scenario which I need to get the sum specific by week.

       

      Ex.

       

      EventUOM

      19-Sep-14

      Fri

      20-Sep-14

      Sat

      21-Sep-14

      Sun

      22-Sep-14

      Mon

      23-Sep-14

      Tue

      OrderUnit232323445364567575
      StartUnit213342423436767323
      ReleaseUnit323223432434342342232

       

      If Event = Order, then it should only sum all the Qty on the last day of the week.

      (Date = >=min(Date of that week)<=max(Date of that week)

      ex: Date = >=19-Sep-14<=21-Sep-14 ; Date = >=22-Sep-14<=23-Sep-14

       

      How can I include this on my set analysis?

       

      Thanks

        • Re: Set analysis question
          Sundarakumar Kalaimani

          Guess you should first use cross table to change of data. Set analysis is applied over a single field.

          pls go thro

          Loading Cross Tables

           

          -Sundar

            • Re: Set analysis question

              Hi,

               

              Dates are not constant, they can be change, I will select what date I want to be last shown in the chart. I can select 22-Sep-14 and the comparison will be like this:

              Date = >=22-Sep-14<=22-Sep-14, i will then get the sum on those dates only.


              however if i select another date: 20-Sep-14 then:

              Date = >=19-Sep-14<=20-Sep-14

            • Re: Set analysis question
              Massimo Grossi

              sum({$

              <Event={Order},Date={">=$(=WeekEnd(max(Date)))<=$(=WeekEnd(max(Date)))"}>

              +

              <Event={Start,Release},Date={">=$(=WeekStart(max(Date)))<=$(=WeekEnd(max(Date)))"}>

              }

              Qty) 

                • Re: Set analysis question

                  Hi,

                   

                  Thanks for the idea, but if that is my formula then the weekstart will only compute for that week. i also need the previous to compute for the last week, even the last 2weeks and so on

                    • Re: Set analysis question
                      jagan mohan rao appala

                      Hi,

                       

                      For current and last week

                       

                      sum({$

                      <Event={Order},Date={">=$(=WeekEnd(max(Date)))<=$(=WeekEnd(max(Date)))"} + {">=$(=WeekEnd(max(Date), -1))<=$(=WeekEnd(max(Date), -1))"}>

                      +

                      <Event={Start,Release},Date={">=$(=WeekStart(max(Date), -1))<=$(=WeekEnd(max(Date)))"}>

                      }

                      Qty)

                       

                      Regards,

                      Jagan.

                      • Re: Set analysis question
                        Massimo Grossi

                        you can use the second parameter of weekstart and weekend

                         

                        from Qlik online help

                         

                        WeekStart(date [, shift = 0 [,weekoffset = 0]])

                        Returns a value corresponding to a timestamp with the first millisecond of the first date ( Monday ) of the calendar week containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.