How to find Latest month's first date?

[silambarasan]

I used monthstart function and It show starting date of month,but that date in not in my transaction date?

Eg;

transaction_Date

3/2/2014

4/2/2014

monthstart(transaction_Date)

output:

1/2/2014

[Anonymous]

Assuming your date is in D/M/YYYY format, then that looks correct to me.

What output date were you expecting ?

[PrashantSangle]

Hi,

explain little bit more..

Expression is correct .

Where are you implementing???

Regards

[HirisH_V7]

Hi,

Check this

=date(floor(monthstart(addmonths(transaction_Date,0))), 'DD/MM/YYYY')

HTH,

Hirish

[tresesco · Accepted solution]

Try like:

=Date(Min({<transaction_Date={'>=$(=MonthStart(Max(transaction_Date)))<=$(=MonthEnd(Max(transaction_Date)))'}>}transaction_Date))

[silambarasan]

Find the latest month's first date.

Latest month here is feb

Eg;

transaction_Date

1/1/2014

3/2/2014

4/2/2014

I want the output should be 3/2/2014

Problems:

The problem which i am faced is,if i am using monthstart function it shows 1/2/2014.

The date 1/2/2014 is not in my transaction_Date. Transaction of feb start from 3/2/2014.

[silambarasan]

Find the latest month's first date.

Latest month here is feb

Eg;

transaction_Date

1/1/2014

3/2/2014

4/2/2014

I want the output should be 3/2/2014

[Anonymous]

try like this:

Test:

Load *,

transaction_Date

from TableName;

Load

Firstvalue(transaction_Date) as StartDate,

lastvalue(transaction_Date) as EndDate

resident Test

group by

month(transaction_Date);

[silambarasan]

Its working good

=Min({$<TRANSACTION_DATE=">=$(=MonthStart(max(TRANSACTION_DATE)))"}>}TRANSACTION_DATE)

[silambarasan]

how can we achieve result using set analysis and not through load script?