Text Object

[shamitshah]

I am trying to get the sum of sales for the latest month in a text box, but I am getting the sum of the year of year to date figure.

I have in the following in script:

Date(MonthStart(Date#([FY KPI], 'DD-MM-YYYY')), 'MM-YYYY')  as FYKPIMonthYear

I have set up a variable:

vMaxBYM

=max(FYKPIMonthYear)

In the text object, I have the following which is resulting in year to date figures instead of the latest month:

=NUM(Sum ({<FYKPIFiscalYear = {$(=Max(FYKPIFiscalYear))},FYKPIMonthYear={"=$(vMaxBYM)"}>} Sales,'#,##0','.',',')

Any idea of the correct expression in the text object to return the latest month's figure?

Thanks

[shamitshah]

Thanks Sunny!

I also tried the following and it seems to work:-

- Created another variable

vMinBYM   =date(addmonths(vMaxBYM,-12),'MM-YYYY')

- Then use the following expression

=Num(SUM({<FYKPIFiscalYear = {$(=Max(FYKPIFiscalYear)-1)}, FYKPIFiscalMonth= ,FYKPIMonthYear={'$(vMinBYM)'}>}Sales),'#,##0.00') 

[sunny_talwar]

Not sure why you need set analysis condition for FYKPIFiscalYear. Year is already handled by the FYKPIMonthYear as this include both month and year information. But having it in there doesn't really harm...