set analysis ... Month =< getfieldselection(Month)?

[Anonymous]

Hi All,

I'm trying to do something with set analysis ... but can't seem to find the right solution

I want to set the Month to something like Month <= selected Month.

Did try the following, but somehow the month is not selected correctly.

sum( {< Month = {"<$(=GetFieldSelections(Month))"} >} [Amount])

Could this have to do with the GetFieldSelections returning a text while my month is expecting a number??

Anyone got suggestions??

thx

Anita

[brenner_martina]

definitly yes, Amount is a field or a column of the chart? Define first a variable

vMonth =(Month)

than the right entry is:

Sum({$< Month = {"<=$(vMonth)"} Amount}

[sparur · Accepted solution]

Sum({$< Month = {"<=$(=max(Month))"} Amount)

[brenner_martina]

yes, is Amount a field or a column of the chart?

Define first a variable

vMonth =(Month)

sum( {$< Month = {"<=$(vMonth)"} >} Amount)

this shows only the values of the selected year, what about all other years? Then you have to use the function Num(Monthname(Datefield)) for an unique monthnumber!

[Anonymous]

I need the selected month (this is not per definition the max month ... ) ...

I have a table showing the amounts etc for the month, with one column for the selected year until and with this month ... so the higher months should not be counted.

I have a selection box for month ... and this selection i want to use for the set analysis.

Problem I guess there is is that the value the GetFieldSelections returns for the month selection is a text containing the month name instead of the month number which I need.

regards

[sparur]

when you select June in Month field then max(Month) will return 6

when you select some values in Month (for example March - August) - max(Month) will return 8

[Anonymous]

Thanks this works ... 😉 Forgot the simple things ... was searching for a to difficult solution here!!

[#8]

How can I get this to say July instead of 7?

[sridhar240784]

Hi,

To get the July instead of 7, you may need to extract the Month from max of date field.

You expression should be as follows.

Sum({$< Month = {"<=$(=Month(max(DateField)))"} Amount)

Hope this helps you.

-Sridhar

[joaquin66]

Hi Anatoly,

I am trying to use below expression but is not working, the result am getting is the sum of all the months, like GLMonth = {blank}

GLMonth field is all the months of the calendar.

Sum({$<Transactionsource={'Open Orders'}, GLMonth = {"<=$(=max(GLMonth)+1)"}>} [Ext Price-F])

script:

LOAD
TempDate AS [G/L Fiscal Date],
week(TempDate) As GLWeek, 
Year(TempDate) As GLYear, 
Month(TempDate) As GLMonth, 
// Day(TempDate) As GLDay, 
  YeartoDate(TempDate)*-1 as GLCurYTDFlag, 
YeartoDate(TempDate,-1)*-1 as GLLastYTDFlag, 
inyear(TempDate, Monthstart($(varMaxDate)),-1) as GLRC12, 
date(monthstart(TempDate), 'MMM-YYYY') as GLMonthYear, 
ApplyMap('QuartersMap', month(TempDate), Null()) as GLQuarter,
Year(TempDate) & '-' &  ApplyMap('QuartersMap', month(TempDate), Null()) as GLYearQtr,
date(monthstart(TempDate), 'YYYY-MMM') as GLYearMonth,
WeekYear(TempDate) & '-' &  Week(weekstart(TempDate)) as GLYearWeek,
Year(TempDate) & '-' &  Day(TempDate) as GLYearDay,
WeekDay(TempDate) as GLWeekDay,
Year(TempDate) & Month(TempDate) as ROPDateKey
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar;