Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Sum({$< Month = {"<=$(=max(Month))"} Amount)
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}
Sum({$< Month = {"<=$(=max(Month))"} Amount)
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!
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
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
Thanks this works ... 😉 Forgot the simple things ... was searching for a to difficult solution here!!
How can I get this to say July instead of 7?
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
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;