topic Re: Count if status is open and valid date has been passed in App Development

Count if status is open and valid date has been passed

Fio — Sat, 16 Nov 2024 19:33:55 GMT

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!

Re: Count if status is open and valid date has been passed

Taoufiq_Zarra — Thu, 28 Nov 2019 14:21:20 GMT

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

Re: Count if status is open and valid date has been passed

Anil_Babu_Samineni — Thu, 28 Nov 2019 14:29:31 GMT

May be this way?

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

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

Re: Count if status is open and valid date has been passed

Fio — Thu, 28 Nov 2019 15:00:07 GMT

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.

Re: Count if status is open and valid date has been passed

Fio — Thu, 28 Nov 2019 15:02:49 GMT

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?

Re: Count if status is open and valid date has been passed

Taoufiq_Zarra — Thu, 28 Nov 2019 15:05:58 GMT

can you share your source file

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

Taoufiq

Re: Count if status is open and valid date has been passed

Fio — Thu, 28 Nov 2019 15:14:44 GMT

Yes, I can.

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

Re: Count if status is open and valid date has been passed

Taoufiq_Zarra — Thu, 28 Nov 2019 15:24:06 GMT

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

Re: Count if status is open and valid date has been passed

Fio — Thu, 28 Nov 2019 15:29:36 GMT

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

But it was still not working.

It is still counting 60.

Re: Count if status is open and valid date has been passed

msteedle — Thu, 28 Nov 2019 15:56:04 GMT

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:

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

Re: Count if status is open and valid date has been passed

Taoufiq_Zarra — Thu, 28 Nov 2019 18:47:48 GMT

Hi,

why do you think that's not right?
the first function returns 34 and the second function returns 26!
if not, what are the correct values?

Taoufiq

Re: Count if status is open and valid date has been passed

Fio — Fri, 29 Nov 2019 08:13:58 GMT

Hi,

I am not saying it is wrong.

I am saying it is not working for me and asking for reasons why it is not working for me, If I have done something wrong or need to change an option or something like that.

Thanks,

Fio

Re: Count if status is open and valid date has been passed

Taoufiq_Zarra — Fri, 29 Nov 2019 08:24:38 GMT

HI,

can you share your Qlikview file (without confidential information)

cheers,

Taoufiq

Re: Count if status is open and valid date has been passed

Fio — Fri, 29 Nov 2019 08:47:25 GMT

Hi,

Thank you for your effort.

Yes, I can share it.

Fio

Re: Count if status is open and valid date has been passed

Taoufiq_Zarra — Fri, 29 Nov 2019 10:19:36 GMT

it was just a problem of space in expression !

now it's OK

can you, please, close the topic if it's good fir you and mark the solution for the other users.

cheers,

Taoufiq

Re: Count if status is open and valid date has been passed

Fio — Fri, 29 Nov 2019 10:29:19 GMT

Yeah,

It is now working in the test App, but not in my normal App.

Could you explain a little bit more to me what you mean with "it was just a problem of space in expression !"?

Thank you!

Fio

Re: Count if status is open and valid date has been passed

Taoufiq_Zarra — Fri, 29 Nov 2019 10:40:46 GMT

🙂

in the measure editor

delete the date : [Offer Valid To Date] and replaced by [Offer Valid To Date] in the wizard by "insert"

sorry i use the french version but it's the same

cheers,

Taoufiq

Re: Count if status is open and valid date has been passed

Fio — Fri, 29 Nov 2019 11:07:17 GMT

Thank you for your explaination.

It is now working! Thank you very much for all your help and your patience.

Cheers,

Fio