3 month Active Partner count

[sunil-kumar5]

Hello All,

I have a below table and I am looking for activating partner's count.

Activating partner count = A partner who installed (take installation date)>=1 license number in last three months

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

Thanks

[Vegar]

Something like the expression below. I assume that your installation date is a dual work a numeric date value. 

Count({<installationdate={">=$(=monthstart(today(), - 2))"} >} DISTINCT Partnername) 

[sunil-kumar5]

Hi Vegar,

Its giving 0 as a result. You didn't mention license number anywhere because it would be the count of license number which is installed >=1.

Hope it clears.

Thanks

[Vegar]

Your sample did not contain any data within the last three months from today. I changed the expression to consider the last three months from your latest installation date instead. I am not really sure what KPI you are looking for, so I added two variants below.

No of partners that have installed something in the last months.

Count({<installationdate={">=$(=monthstart(max(installationdate), - 2))"} >} Distinct Partnername)

No of licenses installed in the last months.

Count({<installationdate={">=$(=monthstart(max(installationdate), - 2))"} >} licensenumber)

[sunil-kumar5]

Hi Vegar,

Actually we have a list of partner's name and we just wanted to check as how many partners are there who installed greater than 1 license in last three months. I have to show the result in KPI as how many partners are active, this is actually the condition if partner is installing >=1 license in last three month that means they are working and active for us.

I have used both the expression that you provided however again getting zero result.

Please advise

[Vegar]

Please verify that your [installationdate] is a dual holding a numeric date and that it is not a string. 

If it is a plain string then you need to interpret it as a date when loading it into your data model. Maybe like below.

LOAD 
	Partnername,
	licensenumber,
	softwaretype,
 	date#(installationdate, 'MMM-YY') as installationdate,
	date#(amcduedate, 'MMM-YY') as amcduedate
inline [
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];

exit script

[sunil-kumar5]

Hi,

It is written as $numeric $integer $timestamp$date but not getting result.

[Vegar]

It could be that you need apostrophes around your $() like I've done below.

Count({<installationdate={">='$(=monthstart(max(installationdate), - 2))' "} >} Distinct Partnername)

[sunil-kumar5]

I am using the same you are suggesting like below:

Count({<InstallationDate={">='$(=monthstart(max(InstallationDate), - 2))' "} >} Distinct [Partner Name]) 

but only getting zero value in my KPI