Active Count and Expired Count

[sunil-kumar5]

Hello All,

I have a below table where I need to count distinct license number for active and expired.

add 60 days in amcduedate and software type should be M only not C. After adding the 60 days formula will be like if (amcduedate < today(), Expired,Active).

I am not able to add software type in my script. Please help

Answer will be : Active -6 and Expired is 3

Partnername	licensenumber	softwaretype	installationdate	amcduedate
A	1656	M	Jan-20	Jan-21
B	1654	C	Feb-18	Feb-19
C	1590	M	Mar-20	Mar-21
D	1361	M	Feb-19	Feb-20
A	1234	M	Feb-20	Feb-21
B	1571	C	Apr-20	Apr-21
C	1981	M	Apr-20	Apr-21
D	1636	C	Mar-20	Mar-21
D	1602	C	Feb-20	Feb-21
A	1921	M	Jan-20	Jan-21
B	1725	M	Feb-19	Feb-20
B	1732	M	Mar-20	Mar-21
C	1706	C	Mar-20	Mar-21
D	1996	M	Mar-18	Mar-20

[GaryGiles · Accepted solution]

A couple of different ways to approach this, depending on what you are presenting.  If Expired and Active are in 2 different KPIs, you could use:

Expired:

count({$<softwaretype={'M'},licensenumber={"=(amcduedate+60<='$(=Date(Today()))')"}>}Distinct licensenumber)

Active

count({$<softwaretype={'M'},licensenumber={"=(amcduedate+60>'$(=Date(Today()))')"}>}Distinct licensenumber)

If you want this in a table, you could use this for your dimension:

=if(amcduedate+60<='$(=Date(Today()))','Expired','Active')

and this for your measure:

count({$<softwaretype={'M'}>}Distinct licensenumber)

[sunil-kumar5]

Hi Gary 

Thanks for providing the solution.

Can you please solve one more situation?

I have to count the partners name who sale >=1 license in last three months.

Please advise.

Thanks