Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
I have a problem with a set analysis.
I need to sum values depends on which month is selected. For example
I have two fields: Final Cost and PPTO
For January: Sum(Final Cost) / Sum({<Month={feb,mar,apr}>}PPTO) // but i need this dynamic
The rule is: Sum of Final Cost of the current month / Sum of PPTO of the next three months
Hope you can help me
Something Like This
Sum({<DateField={">=$(=MonthStart(Max(today())))"}>}Final Cost) / Sum({<DateField={">$(=MonthEnd(Max(today())))<=$(MonthEnd(AddMonths(Max(today()),4)))"}>}PPTO)
Next three months? Are you assuming that Jan is selected?
Are you using Master Calendar? I believe that will be easier.
//This will be next three month excluding current month.
if(InQuarterToDate(TempDate, today(),-3), 1, 0) as NextQTRFlag,
if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,
Insert above syntax in your master script if you have one.
And
sum({<CurMonthFlag={1}>}[Final Cost])/sum({<NextQTRFlag={1}>}[PPTO])
This should work I guess.