Hi friends,
I am trying to include and If condition inside a count measure
Can you please give me your opinion?
The count that I have built is:
Count( distinct {<[GSDP.termstartdate_vp.autoCalendar.Year],[GSDP.termstartdate_vp.autoCalendar.Month],
CRM.servicetype={'Optional'},CRM.I_CMPGN={'9098'},CRM.contact_date ={"<=$(Date(Max(CRM.termstartdate)))"} >} VIN_)
This KPI represents the count of Users (VIN_) filtered by some features as CRM.servicetype and I_CMPGN, the Issue is that the campaign '9098' was launched on Jun the 3rd of 2022, so when I select for example the month May 2022 this KPI should be showing 0 counts, I have added that set analysis CRM.contact_date ={"<=$(Date(Max(CRM.termstartdate)))"} but is not working, I have a fact table requested by the client, where I have applied a measure that makes what I am looking for, and is this:
=if(CRM.contact_date=aggr(max(if(CRM.contact_date <= CRM.termstartdate,CRM.contact_date)),VIN_),CRM.OFFER_VERSION_DESC1)
This measure is correctly showing for example when filtering on May 2022 that the count on the table is 0, I am trying to introduce this on the KPI measure but I don't get how to do that, the closest built I have is
Count( distinct {<[GSDP.termstartdate_vp.autoCalendar.Year],[GSDP.termstartdate_vp.autoCalendar.Month],
CRM.servicetype={'Optional'},CRM.I_CMPGN={'9098'},CRM.contact_date =(if(CRM.contact_date=aggr(max(if(CRM.contact_date <= CRM.termstartdate,CRM.contact_date))))) >} VIN_)
I clarify that the CRM.OFFER_VERSION_DESC1 field is the field contains all the campaign names launched, is a similar than the column I_CMPGN,
I thank you if there is any light from you
Regards,
