7 Replies Latest reply: Aug 3, 2017 4:05 AM by Jose Miguel Vilaplana 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

          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

                  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)

                           )



                        • Re: Show trend
                          Jose Miguel Vilaplana

                          I'm sorry, it's true addweeks doesn't exist

                           

                          And what about the average, it works now? With 21 days?

                           

                          Probably you must to add the same Set Analysis to the Count function

                            • Re: Show trend
                              M M

                              Well, it doesn't work neither for 21 days.
                              I still have the same problem.

                               

                              I know precisely where the issue is :

                              count ( {<Date={">=$(=date(max(Date)-20))<$(=max(Date)+1)"}> } Date)

                               

                              This formula should give 21 as a result but when I select for example a week, it keeps giving me 7.