Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
silambarasan
Creator II
Creator II

How to find Latest month's first date?

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try like:

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

View solution in original post

11 Replies
Anonymous
Not applicable

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
HirisH_V7
Master
Master

Hi,

Check this

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

HTH,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
tresesco
MVP
MVP

Try like:

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

silambarasan
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Not applicable

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
Creator II
Creator II
Author

Its working good

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

silambarasan
Creator II
Creator II
Author

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