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

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

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_IDRC_IDRC_StartRC_End
10001101/01/202124/03/2021
10001225/03/202131/12/2022
10002501/01/202124/03/2021
10003701/01/202124/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_IDRC_IDRC_StartRC_End
10002501/01/202124/03/2021
10003701/01/202124/03/2021

 

Your help would be appreciated.

 

Thanks in advance! 

Labels (3)
2 Solutions

Accepted Solutions
GaryGiles
Specialist
Specialist

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.

View solution in original post

Mystical_jedi
Contributor II
Contributor II
Author

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 !

View solution in original post

7 Replies
GaryGiles
Specialist
Specialist

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
Contributor II
Contributor II
Author

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.

Mystical_jedi_0-1616625558196.png

The yellow highlighted ones are the old RC_ID's.

How can I remove this?

 

Thanks in advance.

Mystical_jedi
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

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
Contributor II
Contributor II
Author

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

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)