7 Replies Latest reply: Aug 3, 2017 4:05 AM by Jose Miguel Vilaplana Pascual RSS

    Show trend

    M M

      Dear Qlik Users,

       

      I have many issues .

       

      I am trying to implend a trend for the receipt of files of the week selection compared with the last 10 weeks.

      So I did not find solutions to :

      • ignore the selection (I have three filters : Year, Month, Week) : I only care about the max(Date) of my selection.
      • select 10 weeks.

       

      Here is an example of a trend for the receipt of files last 3 days selection with the 21 days selection.

       

      (

           avg({$<[Date Selection]={">=$(=weekstart(max(Date Selection)))<$(=weekend(max(Date Selection)+1))"}>} aggr(

                count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

                Date))

      -

           avg({$<[Date Selection]={">=$(=date(max(Date)-20))<$(=max(Date)+1)"}>} aggr(

                count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

                Date))

      )

      /

      avg({$<[Date Selection]={">=$(=date(max(Date)-20))<$(=max(Date)+1)"}>} aggr(

           count(distinct if([Date of receipt]) = [Date Selection], [File Numer])),

           Date))

       

      Thank you.

        • Re: Show trend
          Jose Miguel Vilaplana Pascual

          Hi,

           

          To ignore the selection, change the $ for a 1 in the set analysis

           

          (

               avg({1<[Date Selection]={">=$(=weekstart(max(Date Selection)))<$(=weekend(max(Date Selection)+1))"}>} aggr(

                    count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

                    Date))

          -

               avg({1<[Date Selection]={">=$(=date(max(Date)-20))<$(=max(Date)+1)"}>} aggr(

                    count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

                    Date))

          )

          /

          avg({1<[Date Selection]={">=$(=date(max(Date)-20))<$(=max(Date)+1)"}>} aggr(

               count(distinct if([Date of receipt]) = [Date Selection], [File Numer])),

               Date))


          And for the weeks, you can use the addweek function: https://community.qlikview.com/thread/16976


          Regards

            • Re: Show trend
              M M

              Thank you Jose Miguel,

               

              Concerning a month or year selection, it works perfectly : it is ignoring selection.

              But concerning a week, it does not work : the average for 21 days always calculates the average for the week selection (7 days).

                • Re: Show trend
                  Jose Miguel Vilaplana Pascual

                  Hi

                   

                  You're selecting one week: ">=$(=weekstart(max(Date Selection)))<$(=weekend(max(Date Selection)+1))"

                   

                  Try with ">$(=addweeks(max(Date Selection), -3))<$(=max(DateSelection))"

                   

                  This will take the last 3 weeks

                    • Re: Show trend
                      M M

                      I have tried it but it seems that the function addweeks do not exist.

                       

                      Jose Miguel, apparently the issue comes from the count.
                      It is hard to find a solution that calculate the average of the last 10 weeks, ignoring every selections but the max(Date Selection) because I need it as a starting point :

                       

                      // here is the average for the last 21 days.

                      avg({1<[Date Selection]={">=$(=date(max(Date)-20))<$(=max(Date)+1)"}>}

                                aggr(

                                     count(distinct if([Date of receipt]) = [Date Selection], [File Number])),

                                Date)

                           )