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: 
Fio
Contributor II
Contributor II

Count if status is open and valid date has been passed

Hello community,

 

I am developing an App and want to count the quotes if the status is open and the valid date has been passed by today.

The Data looks like this:

Offer IDOffer Valid to DateOffer Status
101/01/2019Open
231/12/2019Positive
302/01/2019Open
431/12/2019Open

 

My expression looks like this after I searched for similar questions in the community:

Count({<[Offer Status] = {Open}>} if([Offer Valid To Date]<= Date(Today()), [Offer ID]))

I also want to do the same with quotes that have the status open and are still valid.

 

Can you help me please?

Thank you!

Labels (3)
1 Solution

Accepted Solutions
Taoufiq_Zarra

Hi,

for the first question
lower than today and status Open uses:

=Count( {<[Offer Valid to Date] = {"<=$(=Date(Today(), 'DD/MM/YYYY'))"},[Offer Status]={'Open'}>} [Offer ID])

and for the date greater than today and status Open uses

Count( {<[Offer Valid to Date] = {">$(=Date(Today(), 'DD/MM/YYYY'))"},[Offer Status]={'Open'}>} [Offer ID])

 

datt.PNG

cheers,

Taoufiq

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

17 Replies
Taoufiq_Zarra

Hi,

for the first question
lower than today and status Open uses:

=Count( {<[Offer Valid to Date] = {"<=$(=Date(Today(), 'DD/MM/YYYY'))"},[Offer Status]={'Open'}>} [Offer ID])

and for the date greater than today and status Open uses

Count( {<[Offer Valid to Date] = {">$(=Date(Today(), 'DD/MM/YYYY'))"},[Offer Status]={'Open'}>} [Offer ID])

 

datt.PNG

cheers,

Taoufiq

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Anil_Babu_Samineni

May be this way?

Count({<[Offer Status] = {'Open'}>} if([Offer Valid To Date]<= Date(Today(),'DD/MM/YYYY'), [Offer ID]))

Or

Count({<[Offer Status] = {'Open'}, [Offer Valid To Date]={"=$('<=' &Date(Today()))"} >} [Offer ID])

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Fio
Contributor II
Contributor II
Author

Thank you @Taoufiq_Zarra ,

Your solution:

 

=Count( {<[Offer Valid to Date] = {"<=$(=Date(Today(), 'DD/MM/YYYY'))"},[Offer Status]={'Open'}>} [Offer ID])

 

 


Doesn't work for me. It counts all open quotes.

The format in the excel sheet is set to "date" and the format Qlik sense loads it into it is also correct: DD/MM/YYYY. Do I need to change some options or something like that?

 

I am a new to Qlik Sense.

Fio
Contributor II
Contributor II
Author

Thank you  @Anil_Babu_Samineni 

Your solution:

Count({<[Offer Status] = {'Open'}>} if([Offer Valid To Date]<= Date(Today(),'DD/MM/YYYY'), [Offer ID]))

also counts all open quotes. 

And your second solution:

Count({<[Offer Status] = {'Open'}, [Offer Valid To Date]={"=$('<=' &Date(Today()))"} >} [Offer ID])

counts 0, but there have to be 34 passed open quotes.

 

Do you have another solution or things I need to check?


 

Taoufiq_Zarra

can you share your source file

both functions work very well for me with the sample data you shared at the beginning

datt.PNG

Taoufiq

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Fio
Contributor II
Contributor II
Author

Yes, I can.

I deleted some of the information, that I don't want to share, but I left the column titles.

 

Taoufiq_Zarra

Ohh Fio 🙂

in the sample data you shared[Offer Valid to Date], "t" in "to" is lowercase, but in the source file it is capitalized.
so it's normal, will ignore the date condition.

the new function

Count( {<[Offer Valid To Date] = {"<=$(=Date(Today(), 'DD/MM/YYYY'))"},[Offer Status]={'Open'}>} [Offer ID])

and

Count( {<[Offer Valid To Date] = {">$(=Date(Today(), 'DD/MM/YYYY'))"},[Offer Status]={'Open'}>} [Offer ID])

datt.PNG

cheers,

Taoufiq

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Fio
Contributor II
Contributor II
Author

Yeah, I have seen that and corrected that in my App.

But it was still not working.

It is still counting 60.

 
 
 
msteedle
Luminary Alumni
Luminary Alumni

I would recommend creating a field in your data model that stores this status, and then the Set Analysis expression to count those in the UI would be very simple. It may also be handy to categorize the other situations in the same derived field, ex. 'Open Valid', 'Open Expired', ...

2 reasons:

  1. Set Analysis is very picky about date formats, whereas the same condition in the script (or any IF statement) will only care about the values themselves.
  2. I think it is preferable to push this to the script, anyway. The status as of the date you are viewing  the app (Today when used in an expression) can be out of sync with the underlying data. The status and date as of when it was reloaded (Today when used in the script) will be in sync with one another.