topic Create a table which lists ID that are going to expire in 30 days in App Development

Create a table which lists ID that are going to expire in 30 days

Mystical_jedi — Sat, 16 Nov 2024 00:23:22 GMT

Hello Gurus

I am trying to create a table which shows all the ID which are about to expire in the next 30 days. The expression also needs to see if a new date has already been set up for the ID. These do not have to be represented on the table.

Example:

Meter_ID	RC_ID	RC_Start	RC_End
10001	1	01/01/2021	24/03/2021
10001	2	25/03/2021	31/12/2022
10002	5	01/01/2021	24/03/2021
10003	7	01/01/2021	24/03/2021

As It can be seen MeterID 10001 has already a new RC in place, but MeterID 10002 and 10003 don't.

I need this information in my table:

Meter_ID

RC_ID

RC_Start

RC_End

10002	5	01/01/2021	24/03/2021
10003	7	01/01/2021	24/03/2021

Your help would be appreciated.

Thanks in advance!

Re: Create a table which lists ID that are going to expire in 30 days

GaryGiles — Wed, 24 Mar 2021 21:48:00 GMT

Try this:

=aggr(Only({$<RC_ID={"=(RC_End>='$(=Date(Today()))' and RC_End<='$(=Date(Today()+30))')"},
Meter_ID={"=(max(RC_End)>='$(=Date(Today()))' and max(RC_End)<='$(=Date(Today()+30))')"}>} Meter_ID), Meter_ID)

The RC_ID= clause will identify those RCs that are expiring in the next 30 days. The Meter_ID= clause will include only Meter_IDs whose maximum RC_End is with the next 30 days. Together, it should give you the results you are looking for.

Re: Create a table which lists ID that are going to expire in 30 days

Mystical_jedi — Wed, 24 Mar 2021 22:40:14 GMT

Hi @GaryGiles ,

Thank you so much for your help.

I am getting the list of MeterID's for the next 30 days. But there is one issue, I am also getting the past RC_ID for the past date for the same MeterID.

The yellow highlighted ones are the old RC_ID's.

How can I remove this?

Thanks in advance.

Re: Create a table which lists ID that are going to expire in 30 days

Mystical_jedi — Wed, 24 Mar 2021 23:20:25 GMT

Actually I found a solution to this.

I added an expression the RC_End field:

if (today()<=RC_End, RC_end)

and I hid all the Null values after applying this.

My problem has been solved!

Thank you @GaryGiles !

Re: Create a table which lists ID that are going to expire in 30 days

Mystical_jedi — Wed, 28 Apr 2021 05:45:14 GMT

Hi @GaryGiles ,

Just another question regarding this - I would like to keep this KPI constant.

For example - if I choose an end date(RC_End) on a different page, this KPI's value changes (increases) than the value which was seen without choosing a end date filters.

Is there anyway that I can keep this constant irrespective of any filters being added?

Thanks in advance

Re: Create a table which lists ID that are going to expire in 30 days

GaryGiles — Wed, 28 Apr 2021 11:03:28 GMT

Using an Identifier of 1 at the beginning of your set analysis will cause filters to be ignored for the expression. Something like this (this is based on my original expression; wasn't sure what changes you made):

=aggr({1} Only({1<RC_ID={"=(RC_End>='$(=Date(Today()))' and RC_End<='$(=Date(Today()+30))')"},
Meter_ID={"=(max({1} RC_End)>='$(=Date(Today()))' and max({1} RC_End)<='$(=Date(Today()+30))')"}>} Meter_ID), Meter_ID)

Re: Create a table which lists ID that are going to expire in 30 days

Mystical_jedi — Thu, 29 Apr 2021 00:17:44 GMT

Hi @GaryGiles ,

The change that I had made on the query was that I added count in the beginning and changed 30 to 90 to present value on KPI.

I tried your query, in addition to it, I added COUNT again (also added{1})

Count({1}aggr({1} Only({1<RC_ID={"=(RC_EDate>='$(=Date(Today()))' and RC_EDate<='$(=Date(Today()+90))')"},
ACCOUNT_NUMBER={"=(max({1} RC_EDate)>='$(=Date(Today()))' and max({1} RC_EDate)<='$(=Date(Today()+90))')"}>} ACCOUNT_NUMBER), ACCOUNT_NUMBER))

The value still changes after selecting a date.

I am not sure, if the count is added correctly in the beginning.

Re: Create a table which lists ID that are going to expire in 30 days

GaryGiles — Thu, 29 Apr 2021 11:38:53 GMT

I don't think you need to use the aggr() function if you are just looking for a count KPI. I think the way I was determining the RC_IDs was problematic. I reworked that part of the set analysis. Try this for KPI:

=Count({1<RC_ID=P({1<RC_EDate={">=$(=Date(Today()))<=$(=Date(Today()+90))"}>} RC_ID),
ACCOUNT_NUMBER={"=(max({1} RC_EDate)>='$(=Date(Today()))' and max({1} RC_EDate)<='$(=Date(Today()+90))')"}>} ACCOUNT_NUMBER)