Set Analysis Using Current Date Function

[andrew_sell]

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

[OmarBenSalem]

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

What results does it show now?

[andrew_sell]

Currently it is bringing back 0

[sasiparupudi1 · Accepted solution]

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])

[andrew_sell]

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?

[sasiparupudi1]

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])

[sebastiandperei]

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.

[sunny_talwar]

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])

[poojashribanger]

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