Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andrew_sell
Contributor II
Contributor II

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

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

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

View solution in original post

8 Replies
OmarBenSalem

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

What results does it show now?

andrew_sell
Contributor II
Contributor II
Author

Currently it is bringing back 0

sasiparupudi1
Master III
Master III

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
Contributor II
Contributor II
Author

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
Master III
Master III

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

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
Creator II
Creator II

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