Count if status is open and valid date has been passed

[Fio]

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 ID	Offer Valid to Date	Offer Status
1	01/01/2019	Open
2	31/12/2019	Positive
3	02/01/2019	Open
4	31/12/2019	Open

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!

[Taoufiq_Zarra · Accepted solution]

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

cheers,

Taoufiq

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

[Fio]

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]

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

Taoufiq

[Fio]

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

cheers,

Taoufiq

[Fio]

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

But it was still not working.

It is still counting 60.

[msteedle]

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.