9 Replies Latest reply: Feb 2, 2018 10:38 PM by Joaquin Sanchez

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

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

• ###### AW:set analysis ... Month =< getfieldselection(Month)?

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}

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

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

• ###### Re: set analysis ... Month =< getfieldselection(Month)?

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:

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;

• ###### AW:set analysis ... Month =< getfieldselection(Month)?

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!

• ###### AW:set analysis ... Month =< getfieldselection(Month)?

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

• ###### AW:set analysis ... Month =< getfieldselection(Month)?

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

• ###### AW:set analysis ... Month =< getfieldselection(Month)?

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

• ###### AW:set analysis ... Month =< getfieldselection(Month)?

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

• ###### Re: AW:set analysis ... Month =< getfieldselection(Month)?

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