Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Partner
Partner

Issue with timestamp date format in Set analysis

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.

5 Replies

Re: Issue with timestamp date format in Set analysis

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)

Partner
Partner

Re: Issue with timestamp date format in Set analysis

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.

Re: Issue with timestamp date format in Set analysis

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)

Partner
Partner

Re: Issue with timestamp date format in Set analysis

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

Re: Issue with timestamp date format in Set analysis

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...