8 Replies Latest reply: Apr 2, 2018 3:23 AM by Poojashri Bangera

# Set Analysis Using Current Date Function

Hello Community,

Fairly new to QlikSense and i need some help with a formula that worked for me yesterday, but does not seem to bring back any results this morning and i am thinking it may have something to do with my date function.  I have a dataset that looks like this:

Item-SDI Part Number            End Date               Contract Status

ABC123                                   2/1/2018                         A

ABC456                                   6/31/2018                       I

ABC678                                   6/31/2018                       A

ABC987                                   7/31/2018                       A

I am trying to get a count of all distinct items that are Active and have a Contract End date greater than or equal to the date the user is on the app.  Currently i am using this which i believe worked yesterday, but not this morning:

Count({\$<[End Date] = {">=\$(=Date(Today(),'MM/DD/YYYY'))"}, [Contract Status]= {'A'} >} [Item-SDI Part Number])

Any  help would be more than appreciated!

Andrew

• ###### Re: Set Analysis Using Current Date Function

By it's not working? what do u mean?

What results does it show now?

• ###### Re: Set Analysis Using Current Date Function

Currently it is bringing back 0

• ###### Re: Set Analysis Using Current Date Function

What is the date format in END Date?

Does your expression work if you hardcode the date in it?

Count({<[End Date]={">=03/31/2018"},[Contract Status]={'A'}>}Distinct [Item-SDI Part Number])

• ###### Re: Set Analysis Using Current Date Function

Create a varaible

vToday with value Date(Today(),'M/DD/YYYY')

in your set expression, Count({<[Contract Status]={A},[End Date]={"<=\$(vToday)"}>}Distinct [Item-SDI Part Number])

• ###### Re: Set Analysis Using Current Date Function

Just can't seem to get this to work. I put this in for my variable:

vToday: Date(Today(), 'MM/DD/YYYY')

and this was my expression:

Count({<[End Date]={">=\$(vToday)"},[Contract Status]={'A'}>}Distinct [Item-SDI Part Number])

just not seeming to have any luck with it?

• ###### Re: Set Analysis Using Current Date Function

Are you sure that you have "End Date" values that are greater or same that 3/30/2018 with Active status?

Yesterday (last day when you have wrote the message), [End Date] = {">=\$(=Date(Today(),'MM/DD/YYYY'))"} means >= 03/29/2018, but now (when you have wrote the message) [End Date] = {">=\$(=Date(Today(),'MM/DD/YYYY'))"} means >=03/30/2018. Please, make sure you have data that is availabe for the results you need.

• ###### Re: Set Analysis Using Current Date Function

Not sure if it makes any difference, but can you try with the format M/D/YYYY instead of MM/DD/YYYY

Count({\$<[End Date] = {">=\$(=Date(Today(),'M/D/YYYY'))"}, [Contract Status]= {'A'}>} [Item-SDI Part Number])

• ###### Re: Set Analysis Using Current Date Function

i checked it's working fine for me for same expression