Discussion board where members can get started with QlikView.
I have a field which has timestamp value '4/8/2010 2:43:29 PM'. I wanted to get the month & year combination out of all dates. I have tried both the logics but still not getting the desired result.
1. date(monthstart(date#(ClaimSubmitDate, 'mm/dd/yyyy hh:mm:ss tt')), 'MMM YYYY') as Claim_Month,
2. date(monthstart(timestamp#(ClaimSubmitDate, 'mm/dd/yyyy hh:mm:ss tt')), 'MMM YYYY') as Claim_Month,
Could anyone please help me to understand if I am missing anything.
Attaching the sample qvw and data file
Go to Solution.
Based on the sample file you provided... this should work because your date is already understood as date by QlikView... you don't need an interpretation function here (Date#() or TimeStamp#())
Date(MonthStart(ClaimSubmitDate), 'MMM YYYY') as Claim_Month,
View solution in original post
May be try this
Date(MonthStart(TimeStamp#(ClaimSubmitDate, 'M/D/YYYY h:mm:ss TT')), 'MMM YYYY') as Claim_Month,
few things to note
1) M is for Month and m is for minute... in TimeStamp#() you use m for month
2) It seems that there are two spaces between Date and time portion... if that is true, make sure to use two spaces within TimeStamp#() interpretation or you can try this
Date(MonthStart(Date#(SubField(ClaimSubmitDate, ' ', 1), 'M/D/YYYY')), 'MMM YYYY') as Claim_Month,
Thank u so much!! U r always a Saviour!!