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

[Mystical_jedi]

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! 

[GaryGiles · Accepted solution]

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.

[Mystical_jedi]

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.

[Mystical_jedi · Accepted solution]

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 !

[Mystical_jedi]

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

[GaryGiles]

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)

[Mystical_jedi]

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.

[GaryGiles]

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)