Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi, i need some help writing a couple of expressions..
I am reporting on a period field and want to also report on the previous period and the same period but for the previous year.
So eg..
the user selects period 4 from a Period field and year 2012 from a FiscalYear field
I have an expression Sum(Amount) which obviously shows the total Amount for period 4 2012
I want to also see the sum of Amount for Period 2 in 2012
and the sum of Amount for Period 4 in 2011
Can anyone help please?
Previous year is easy: sum({<FiscalYear={'$(=max(FiscalYear)-1)'}>}Amount)
Previous period for every period but period 1: sum({<Period={'$(=max(Period)-1)'}>}Amount). Period 0 doesn't exist, but should be period 4 of the previous year. To handle that correctly you would need a serial period number field that doesn't reset per year. You could create a new field for that in the script. If your data is ordered by year and period already you can use something like autonumber(FiscalYear*10+Period) as SerialPeriod. The expression would then become:
sum({<FiscalYear= ,Period= ,SerialPeriod={'$(=max(SerialPeriod)-1)'}>}Amount)
Thanks, the year was so simple! i thought i had to also include the period number but i didnt.
I also need another one giving me YTD. so i need to select Period >=01 <=selected period
Is this an easy one too?
The formula for that is:
sum({<Period={'>=1<=$(=max(Period))'}>}Amount)
Please find attached that show an example of getting last year, last period, and YTD.
Hope this helps!