Issue with timestamp date format in Set analysis

[mhappiee]

Hi

I have an issue with the date format in set expression ,

The issue is

I have a field which consists timestamp in the date field and I just wanted to compare only the date part with a variable inspite of doing it with the whole times stamp i.e from the below example I should use only 6/27/2017 should be compared with the Variable defined

I was unable to find that out a proper solution for that ,so changed the variable to have a timestamp, but this case can go wrong if the timestamp in field has time other than 12:00:00 AM

Field:

ORDER_CLOSED_DATE = 6/27/2017 12:00:00 AM

Variable:

vCurntOrdrclsddate =Timestamp(Timestamp#(today(),'M/D/YYYY')-2,'M/D/YYYY hh:mm:ss TT')

Set expression:

if(Ord_Status='Cancelled/Withdrawn',count({<ORDER_CLOSED_DATE={'$(=vCurntOrdrclsddate)'}

  ,ORDER_PRODUCT={'Full','','-'}

  ,ORDER_STATUS={'CANCELLED','','-'}>}ORDER_NBR)

Can somebody help me out in getting a resolution for this.

Kind Regards,

Anand.

[sunny_talwar]

May be use this in the script

LOAD Date(Floor(ORDER_CLOSED_DATE)) as ORDER_CLOSED_DATE

and then this for your variable

vCurntOrdrclsddate = Date(Today()-2)

and finally this

if(Ord_Status='Cancelled/Withdrawn',count({<ORDER_CLOSED_DATE = {'$(=vCurntOrdrclsddate)'}

  ,ORDER_PRODUCT={'Full','','-'}

  ,ORDER_STATUS={'CANCELLED','','-'}>}ORDER_NBR)

[mhappiee]

Hi Sunny

Thanks for the prompt reply

But, I don't want to change the date format in the load script I want it to be done in the set analysis  only

Kind Regards,

Anand.

[sunny_talwar]

Then may be like this

LOAD TimeStamp(ORDER_CLOSED_DATE) as ORDER_CLOSED_DATE

and then this for your variable

vCurntOrdrclsddate = TimeStamp(Today()-2)

and finally this

if(Ord_Status='Cancelled/Withdrawn',count({<ORDER_CLOSED_DATE = {'$(=vCurntOrdrclsddate)'}

  ,ORDER_PRODUCT={'Full','','-'}

  ,ORDER_STATUS={'CANCELLED','','-'}>}ORDER_NBR)

[mhappiee]

Hi Sunny,

That is what I said I need to change the variable and get a default time stamp to compare, which I have already implemented as said in the question above.

I just want to compare date part with out modifying the script and only in set analysis by extracting date component from the field.

Anand

[sunny_talwar]

Not entirely sure, but see if this works

if(Ord_Status='Cancelled/Withdrawn',count({<ORDER_CLOSED_DATE = {"=Floor(ORDER_CLOSED_DATE) = vCurntOrdrclsddate"}, ORDER_PRODUCT={'Full','','-'}, ORDER_STATUS={'CANCELLED','','-'}>}ORDER_NBR)

What really help in these times are a sample so that we can look at what exactly you are trying to do...