max month by year in scripting side for looping

[rohitk1609]

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;

[lironbaram]

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;

[eliran]

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.