Set analysis with dates help

[mahitham]

Hi Experts,

Can any one please help me to calculate the below two logics.

I have a date field i.e., Target Date in DD/MM/YYYY format. It contains the dates up to 30/12/2019. 

Requirement:

1.  How to calculate the Count( distinct Id) where Target Date >6Months 

2.  How to calculate the Count( distinct Id) where Target Date >1Month and  Target Date <6Months

Please help me on this.

Thanks in advance.

[dpduran]

Hi @mahitham

First of all, I would turn the date field into number in the script using the num function so it's not that heavy in memory if you haven't already.

num(date#([Target Date], 'DD/MM/YYYY'))

I would define a couple variables for that so you can use them easily in the set analysis.

I'm considering that you want to calculate Target Dates that are > than 6 months from today's / reload date.

The first one would be:

vTargetDate6M
Definition:  = addmonths(floor(num(ReloadTime()), 6)
This variable will contain the date that is exactly 6 months away from the latest Reload date. You can use Today() instead if you want to see against Today's date.

and same for 1 month away.

vTargetDate1M
Definition:  = addmonths(floor(num(ReloadTime()), 1)

Once you have those variables defined, now youy can call it from the Set Analysis like:

Count of distinct Ids with a Target Date greater than 6 months.
Count(<{[Target Date]={'>$(vTargetDate6M)'}}>DISTINCT Id)

if you want those in between 1 month and 6 months then go with:
Count(<{[Target Date]={'>$(vTargetDate1M) <$(vTargetDate6M)'}}>DISTINCT Id)

*Note that depending on how the variables are defined you might need to either doublke expand the variable with $(=$(vTargetDate6M) or $(=vTargetDate6M) as usual...

[sunny_talwar]

Target Date >6 Months from Today or the max target date? From Max Date you can try this

Count(DISTINCT {<[Target Date] = {"$(='>' & Date(AddMonths(Max([Target Date]), -6), 'DD/MM/YYYY'))"}>} Id)

or

Count(DISTINCT {<[Target Date] = {"$(='>' & Date(MonthStart(Max([Target Date]), -6), 'DD/MM/YYYY'))"}>} Id)

If you want it from Today, you can try this

Count(DISTINCT {<[Target Date] = {"$(='>' & Date(AddMonths(Today(), -6), 'DD/MM/YYYY'))"}>} Id)

or

Count(DISTINCT {<[Target Date] = {"$(='>' & Date(MonthStart(Today(), -6), 'DD/MM/YYYY'))"}>} Id)

 For >1 Month and <6 Months... you can do this

Count(DISTINCT {<[Target Date] = {"$(='>' & Date(AddMonths(Max([Target Date]), -1), 'DD/MM/YYYY') & '<' & Date(AddMonths(Max([Target Date]), -6), 'DD/MM/YYYY'))"}>} Id)

or

Count(DISTINCT {<[Target Date] = {"$(='>' & Date(AddMonths(Today(), -1), 'DD/MM/YYYY') & '<' & Date(AddMonths(Today(), -6), 'DD/MM/YYYY'))"}>} Id)

[teiswamsler]

Hi @mahitham

If you use the autogenerated derived calender, you can use following set analisys in the expression.

{< autocalender.Date Target.MonthsAgo = {0,1,2,3,4,5} >}

Br.

Teis