Expression help

[#1]

Ref     YearMonth     Receipt

222      2015 -11              0

333      2015 - 12             0

231      2015 - 12             1

156      2015 - 12             1

Please can you give me a FIXED expression for:

COUNT of 'REF'

WHERE 'YearMonth' is current

and 'Receipt' = 1

[tresesco]

Try:

Count({<Receipt={1}, YearMonth={'$(=Year(Today())&' - '&Num(Month(today())))'}>}REF)

Note: Space before and after '-', you have to be careful about.

[sunny_talwar]

Not sure if you YearMonth field is a date field and how this is getting created in the script, but if it is truely a date field, something like this should work;

Count({<Receipt = {1}, YearMonth = {"$(=Date(Max(YearMonth), 'YYYY - MM'))"}>}REF)

[Anonymous]

may be this:

=Count({<Receipt={'1'}, YearMonth={'$(=max(num(YearMonth))'}>} REF)

[#5]

Unfortunately this has not given me a result as I have records for the current month and nothing is displayed.

[sunny_talwar]

I think the problem might be related to how the YearMonth field is getting calculated in the script. Are you using a function like this?

Date(DateField, 'YYYY - MM') as YearMonth?

[#7]

Hi Sunny

Expression states ok but no result - so this must be the way I have loaded the date.

My YearMonth field in load is:

CreatedOn,  (comes in as a date time stamp from SQL)

Convert(varchar,year([CreatedOn])) + '-' +  convert(varchar,month([CreatedOn])) as YearMonth,

Sorry very new to this!

[#8]

Hi Balraj

Please see my reply to Sunny.....

Still having errors, probably due to date format ?

Thanks

[rubenmarin]

Hi Emma, tresesco expression seems ok, can you ckeck the case sensitivity?. I.e. 'Ref' instead of 'REF'.

Also confirm the YearMonth format, there is an space before and after the dash ( - )?

[sunny_talwar]

Not an expert in SQL, but this seems like text instead of date:

May be try this:

LOAD Date(Date#(YearMonth, 'YYYY-MM'), 'YYYY-MM') as YearMonth;

SELECT SQL

CreatedOn,  (comes in as a date time stamp from SQL)

Convert(varchar,year([CreatedOn])) + '-' +  convert(varchar,month([CreatedOn])) as YearMonth,

and then this expression:

Count({<Receipt = {1}, YearMonth = {"$(=Date(Max(YearMonth), 'YYYY-MM'))"}>}REF)