Count of exiting Customers who completed the survey in their final month or the previous month.

[onkarkulkarni]

Hi Team, I am working on a requirement, where I have fields- personID,  ReportingMonth ('MMM YY'), Exit(1/0), SurveyCompleteFlag(1/0)

My requirement is to - Count the number of exiting customers in the selected period (ReportingMonth) who completed the survey in their last month of service or the month prior. Reporting Month is filter and the user can select multiple reporting months.

I could get the count of the exiting Customers who completed the survey in their last month of service as below:

Count({<Reportingmonth={"$(selectedreportingmonth)"},SurveyCompletedFlag={'1'},Exit={'1'}>} distinct PersonID)

But not sure how to check if the SurveyCompletedFlag={'1'} for the Previous month for the same customer.

Appreciate any guidance, Thanks !

[sandeep-singh]

Try this:

vCurrentMonth = Date#(max(ReportingMonth),'MMM-YY')

vPreviousMonth =Date#(addmonths(max(ReportingMonth), -1), 'MMM-YY')

For previous month

count({<ReportingMonth={'$(=$(vPreviousMonth))'}, SurveyCompletedFlag={'1'},Exit={'1'}>}distinct PersonID)

For current month

count({<ReportingMonth={'$(=$(vCurrentMonth))'}, SurveyCompletedFlag={'1'},Exit={'1'}>}distinct PersonID)

[Rams-]

Hi,

you can directly use this expression:

For previous month

count({<ReportingMonth={'$(=$(date(max(ReportingMonth),'MMM-YY')))'}, SurveyCompletedFlag={'1'},Exit={'1'}>}distinct PersonID)

For current month

count({<ReportingMonth={'$(=$(date(addmonths(max(ReportingMonth), -1), 'MMM-YY')))'}, SurveyCompletedFlag={'1'},Exit={'1'}>}distinct PersonID)

Else this will also work

vCurrentMonth = Date#(max(ReportingMonth),'MMM-YY')

vPreviousMonth =Date#(addmonths(max(ReportingMonth), -1), 'MMM-YY')

For previous month

count({<ReportingMonth={'$(=$(vPreviousMonth))'}, SurveyCompletedFlag={'1'},Exit={'1'}>}distinct PersonID)

For current month

count({<ReportingMonth={'$(=$(vCurrentMonth))'}, SurveyCompletedFlag={'1'},Exit={'1'}>}distinct PersonID)

Hope this helps,

help user find answers ! don't forget to mark  a solution that work for you and click the like button!