2 Replies Latest reply: Apr 2, 2017 4:07 PM by Eliran Sanor RSS

    max month by year in scripting side for looping

    Rohit Kumar

      Hi,

       

      I am facing a problem where I need to run a loop between minimum and maximum month w.r.t different years but whenever I am trying to run below code, it takes maximum month w.r.t to maximum Year. My use case it should take maximum month in variable w.r.t Year respectively.I am writing the code where I am trying to cumulative sum on months. In front end it is possible as max(<{Year={$(=max(Year))}}>month) but I want to do this on scripting side. I am attaching the data file also. vMaxMnth this should change w.r.t Years

       

      Data:

      LOAD FMonth,

        Year,

           Value

      FROM [xxxxx\Book11.xlsx] (ooxml, embedded labels, table is Sheet1)

      ;

      MIN_MAX:

      LOAD

        min(FMonth) as MinMnth,

        max(FMonth) as MaxMnth,

        Year

       

      RESIDENT Data

      group by Year;

       

      let vMinMnth = peek('MinMnth',0,'MIN_MAX');

      let vMaxMnth = peek('MaxMnth',0,'MIN_MAX');

       

      FOR i = $(vMinMnth) to $(vMaxMnth)


      SALES1:

      LOAD

        $(i) as FMonth,

        Sum(Value) as Value1,

        Year

        Resident Data

        Where FMonth <= $(i)

        group by Year;

        • Re: max month by year in scripting side for looping
          Liron Baram

          hello

          i i understand correctly

          this script will calculate the accumulative sum for each year

           

           

          DataTemp:

          LOAD [FMonth],

            [Year],

            [Value]

          FROM [lib://Downloads/Book11.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

           

          Data:

          load *,

               if([Year]=previous([Year]),RangeSum(peek('ValueYTD'),[Value]) ,[Value]) as ValueYTD

          Resident DataTemp

          Order By [Year],[FMonth];

           

           

          drop Table DataTemp;

          • Re: max month by year in scripting side for looping
            Eliran Sanor

            Hi,

             

            The answer Liron gave you will do the trick.

            But just to elaborate on why your script didn't work:

            You populate the variables with only the first row - min/max of first year.

            You didn't include a 'next i' code to continue the loop.

            That's why you end up with only two rows of value of the first month for both the years.

             

            Hope it helps,

            Eliran.