6 Replies Latest reply: Feb 26, 2016 7:39 AM by Lidia Vonk RSS

    set analysis - last year more then 1 week

    Lidia Vonk

      Hello who can help me with this one

      posible calender selections

      Year = Year

      periode= Periode

      FiscalYear-Wk = YrWk.

       

      When i select 1 YrWk everything reacts as expected

      When i select more then 1 week in the same fiscal year - last year does respond with 0

       

      Data last fiscal year is available.

      test set analysis las year.png

      in table

      DG # 2016 =if(  sum( {$< $(vOnlyLastYear) >} [Value return line])  =0, null(),  sum( {$< $(vOnlyLastYear) >} [Quantity return line]) )

      DG # 2015= if(  sum( {$< $(vOnlyLastYear) >} [Value return line])  =0, null(),  sum( {$< $(vOnlyLastYear) >} [Quantity return line]) )

       

      in script

      let vOnlyCurYear= replace('Year={"~(=Only( [Fiscal year]) )"}',  '~','$');
      let vOnlyLastYear= replace('Year={"~(=Only( [Last Fiscal year]) )"} ,YrWk={"~(=LaYrCuWk)"}, Period= ','~','$');

       

      Article noYearWeekYrWkFiscal yearsum(  [Quantity return line])
      Total70
      10057201662016-06201621
      10057201672016-07201619
      10057201562015-06201516
      10057201572015-07201514

       

      thanks in advance

      Lidia

        • Re: set analysis - last year more then 1 week
          Cesar Accardi

          Hi Lidia,

           

          I believe the issue lies on this set modifier YrWk={"~(=LaYrCuWk)"}, try using a function to concatenate the multiple values when you have more than 1 week selected:

           

          YrWk={"~(=Concatenate(LaYrCuWk,','))"},

           

          --Cesar

            • Re: set analysis - last year more then 1 week
              Lidia Vonk

              Hi Cesar,

              Thanks for the quick reaction,

               

              for fast test i changed it in the object to

              =sum( {$<

                             Year={"$(=Only( [Last Fiscal year] )) "},

                             YrWk={"$(=Concatenate(LaYrCuWk, ',')) "}

                        >} [Quantity return line] )

               

              it gives a 0 

              aswel on 1 selection as on multiple selection

               

              do you have any other idea's  there welcome

              can it be that YrWk is a string and not a number?

               

               

              below calendar

              [$(vSection)]:

              LOAD

                     // Keys

                     left(YrWk,4)&num(Right(YrWk,2),'00') as %YrWk,

                    //Calculated

                     left(YrWk,4)-1&'-'&num(Right(YrWk,2),'00') as LaYrCuWk,

                     Year-1 as LastYear,

                     [Fiscal year] -1 as [Last Fiscal year],

                      // Attributes

                       *

              FROM $(vSourcePath1)$(vSourceQvd1).qvd (qvd);

               

              field example

              Date18-02-2016
              Fiscal year2016
              Month02
              Period2
              Week7
              Year2016
              YrMth2016-02
              YrPer2016-02
              YrWk2016-07
              Day18
              WeekDayThu
              DateSeqNr42418
              YrPerSeqNr66
              YrWkSeqNr269
              QuarterQ1
              YrQtr2016-Q1
              %YrWk201607
              _cntCalendar1
              _indCalenderType4W
              Last Fiscal year2015
              LastYear2015
              LaYrCuWk2015-07
              T009B.BUMON02
              YrMthSeqNr62
              YrQtrSeqNr22

               

              thanks again

              Lidia

                • Re: set analysis - last year more then 1 week
                  Cesar Accardi

                  Hi Lidia,

                   

                  No problems. Maybe try replacing it with explicit values to check if the issue is indeed with this set modifier:

                   

                  =sum( {$<

                                 Year={"$(=Only( [Last Fiscal year] )) "},

                                 YrWk={"2016-07","2016-06"}

                            >} [Quantity return line] )

                    • Re: set analysis - last year more then 1 week
                      Lidia Vonk

                      that works for the next options

                       

                      =sum( {$<

                                    //        Year={"$(=Only( [Fiscal year] )) "},

                                     YrWk={"2016-07","2016-06"}

                                >} [Quantity return line] )

                      =sum( {$<

                                    //             Year={"$(=Only( [Last Fiscal year] )) "},

                                     YrWk={"2015-07","2015-06"}

                                >} [Quantity return line] )

                       

                      but NOT with concatenate , not for 1 selection and not by multiply selections

                      =sum( {$<

                                   //           Year={"$(=( [Last Fiscal year] )) "},

                                   //             YrWk={"2015-07","2015-07"}


                                    YrWk={"$(=Concatenate(LaYrCuWk,','))"}

                       

                                >} [Quantity return line] )

                       

                      =sum( {$<

                                   //            Year={"$(=( [Fiscal year] )) "},

                                   //             YrWk={"2016-07","2016-07"}

                       

                                   YrWk={"$(=Concatenate(YrWk, ',')) "}

                              

                            >} [Quantity return line] )

                       

                      any idea's left

                      • Re: set analysis - last year more then 1 week
                        Lidia Vonk

                        i found the solution

                         

                        YrWk={"$(=concat(DISTINCT LaYrCuWk,' ","  '))"}

                         

                        Thanks for helping

                        Lidia