Expression total count for past 12 Months

[mohdhaniff]

Hi,

Currently, I am using below expression for getting total for a specific date for past months:

count({$<[Disbursement MthStart]={"$(=Date(addmonths(max([Disbursement MthStart]),-11),'DD-MMM-YYYY'))"}>} if(Product='AFFSTR0001', [Account No]))

From the script I can only having a result for total of 1 [Disbursement MthStart] only. Can anybody help me to modified the script for me getting the total value between past 11 months until current (Max)?

[sunny_talwar · Accepted solution]

May be this

Count({$<[Disbursement MthStart] = {"$(='>=' & Date(AddMonths(Max([Disbursement MthStart]),-11), 'DD-MMM-YYYY') & '<=' & Date(Max([Disbursement MthStart]), 'DD-MMM-YYYY'))"}>} if(Product='AFFSTR0001', [Account No]))

Or this (converting if statement to set analysis

Count({$<[Disbursement MthStart] = {"$(='>=' & Date(AddMonths(Max([Disbursement MthStart]),-11), 'DD-MMM-YYYY') & '<=' & Date(Max([Disbursement MthStart]), 'DD-MMM-YYYY'))"}, Product *= {'AFFSTR0001'}>} [Account No])

[mohdhaniff]

Great!!

New knowledge for me... Thanks Sunny.

Regards