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

[Anil_Babu_Samineni]

May be this?

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

Or

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

[shamitshah]

Hi Anil,

Both are returning year to date figures.

I tried the following which seems to work:

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

Now, I am trying to get the comparative for the prior year (same month) which is where I am having an issue. Any idea?

Thanks.

[Anil_Babu_Samineni]

That;s decimal format issue. That's not big deal

try for prior year like below. It will display last year of same month data and change variable to same as year with prefix of -1

=NUM(Sum ({<FYKPIFiscalYear = {"$(=AddYears(Max(FYKPIFiscalYear),-1))"},FYKPIMonthYear={">=$(vMaxBYM)"}>} Sales),'#,##0.00')

[sunny_talwar]

May be this

Sum({<FYKPIFiscalYear = {$(=Max(FYKPIFiscalYear))}, FYKPIMonthYear = {">=$(=Date(AddYears(vMaxBYM, -1), 'MM-YYYY'))"}>} Sales)

[shamitshah]

Hi Sunny & Anil

Please see attached sample.

I now have 2 text objects - one showing the year to date figure and the other showing the month to date figure for the current year.

I am having an issue with getting the month to date figure for the prior year in a text box (i.e for April 2016 which should be 300)

Anil, I did not quite follow your suggestion.

Any idea?

Thanks

[shamitshah]

The first text box should read Sales YTD and not Sales MTD.

[Anil_Babu_Samineni]

May be use for MTD
=Sum({<FYKPIMonthYear = {'$(=Min(FYKPIMonthYear,4))'}>}Sales)

[shamitshah]

I should clarify further - ideally it should update automatically with the figures for max month of the current year and the corresponding period for the prior year as and when the data is reloaded.

Thanks

[sunny_talwar · Accepted solution]

May be this

=Num(Sum({<FYKPIMonthYear = {"$(=Date(AddYears(vMaxBYM, -1), 'MM-YYYY'))"}>} Sales),'#,##0.00')