topic Re: max month by year in scripting side for looping in Management & Governance

max month by year in scripting side for looping

rohitk1609 — Sun, 02 Apr 2017 19:09:33 GMT

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

lironbaram — Sun, 02 Apr 2017 19:55:18 GMT

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 — Sun, 02 Apr 2017 20:07:36 GMT

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.