Text Object

[shamitshah]

Hi,

I have the following in a text object, but not getting any value

=num(AGGR(SUM(total{$<RecordMonthYr={"=$(vMaxRMY)"},SKH_Source={'SO'}>}SKH_Quantity),[Item Number]),'#,##0','.',',')

The variable vMaxRMY has been set as =date(max(RecordMonthYr),'MM/YYYY')

Any ideas?

Thanks

Shamit

[vishsaggi]

Hi Shah,

Is your recordMonthYr same format as your variabe vMaxRMY ?

SET vMaxRMY = "=date(max(RecordMonthYr),'MM/YYYY')" ;

And try this:

=num(AGGR(SUM(total{$< RecordMonthYr= {"$(vMaxRMY)"}, SKH_Source={'SO'}>} SKH_Quantity),[Item Number]),'#,##0','.',',')

These are references taken from HIC post: Dates in Set Analysis

Please look into this and try.

[sunny_talwar]

You don't have an outer Aggregation function, what exactly are you trying to get? Can you try one of these?

=Num(Sum({$<RecordMonthYr={"$(=vMaxRMY)"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

or

=Num(Sum({$<RecordMonthYr={"$(=$(vMaxRMY))"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

[shamitshah]

I am trying to get the number of Sales Orders for the latest month.

In the script, I have the following:

MonthName(Date([SKH_RecordDate] + MakeDate(2000,1,1))) as RecordMonthYr

I then set up the variable to get the max RecordMonthYr as =date(max(RecordMonthYr),'MM/YYYY')

In the text object, I wanted to get the number of Sales Orders for the latest month.

Thanks

Shamit

[sunny_talwar · Accepted solution]

I think since you created your field RecordMonthYr using MonthName function, that is what you would need to use in your variable:

RecordMonthYr as = MonthName(Max(RecordMonthYr))

Alternatively, you can change how you create your field RecordMonthYr

Date([SKH_RecordDate] + MakeDate(2000,1,1), 'MM/YYYY') as RecordMonthYr

and then use your current variable definition:

RecordMonthYr as =Date(Max(RecordMonthYr), 'MM/YYYY')

The goal is to have same format on the Left and Right hand sides of the equation. Once you have the above two fixed, try using one of these expressions to see if it finally works or not

=Num(Sum({$<RecordMonthYr={"$(=vMaxRMY)"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

or

=Num(Sum({$<RecordMonthYr={"$(=$(vMaxRMY))"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

[shamitshah]

Thanks Sunny,

Your solutions works perfectly.

I changed the variable to = MonthName(Max(RecordMonthYr))

For the text object ==Num(Sum({$<RecordMonthYr={"$(=vMaxRMY)"}, SKH_Source={'SO'}>} SKH_Quantity), '#,##0','.',',')

Shamit