Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have used the below command to extract the monthend date for date selected.
Logic: =AddMonths(monthend(max(([Calendar Date]),'DD-MMM-YYYY')),-1)
I'm getting the date like '10-02-1987'
Required format:
But my [Calendar Date] format is like '10-Feb-1987'
Please let me know how to convert the date format.
Thanks..
Try:
Sum({$<[Month Abbr]=, Year=, [Quarter Name]=,[Calendar Date]={'>$(=Date(AddMonths(max([Calendar Date]),-1)),'DD-MMM-YYYY') <=$(=Date(max([Calendar Date]),'DD-MMM-YYYY'))'}[Conformed Status] -= {'Monitoring*', 'Effectiveness Check'}>}[# SCAR Open]),'#0.0%')
Try like:
Date(monthend(max([Calendar Date]), -1) ,'DD-MMM-YYYY')
Hi nareshthavidishetty,
Try below logic,
Logic: AddMonths(monthend(date#(max([Calendar Date]),'DD-MMM-YYYY')),-1)
Thanks & Regards,
Arvind Patil
Hi,
I need to use 'Addmonths' for roll back to months.
Thanks..
Hi,
Still getting in 'DD-MM-YYYY' format.
Thanks..
Hi,
Try like this.
SET vPrevMonth =DATE(AddMonths(monthend(max([Calendar Date])),-1),'DD-MMM-YYYY');
LOAD DATE#([Calendar Date],'DD-MMM-YYYY') as [Calendar Date],DATE(DATE#([Calendar Date],'DD-MMM-YYYY'),'DD-MMM-YYYY') as NewDate INLINE [
Calendar Date
10-Feb-1987
10-Mar-1997
10-Apr-2007
10-Feb-2017
10-Dec-1987
];
ohh My bad.
Yes its working.
But when i try to replace the logic with Date(monthend(max([Calendar Date]), -1) ,'DD-MMM-YYYY')
in
Sum({$<[Month Abbr]=, Year=, [Quarter Name]=,[Calendar Date]={'>$(=AddMonths(max([Calendar Date]),-1))<=$(=max([Calendar Date]))'}[Conformed Status] -= {'Monitoring*', 'Effectiveness Check'}>}[# SCAR Open]),'#0.0%')
My code is not working.
Thanks..
Try:
Sum({$<[Month Abbr]=, Year=, [Quarter Name]=,[Calendar Date]={'>$(=Date(AddMonths(max([Calendar Date]),-1)),'DD-MMM-YYYY') <=$(=Date(max([Calendar Date]),'DD-MMM-YYYY'))'}[Conformed Status] -= {'Monitoring*', 'Effectiveness Check'}>}[# SCAR Open]),'#0.0%')