How to add date filter in expression

[Anonymous]

Count({<[IsConverted] = {'false'}>}LeadId)

Here I want to put a condition where in my campaign table, there is a field called "CampaignEndDate", and I want to get LeadId count where date is greater than  "CampaignEndDate". Pl help.

[PrashantSangle]

Hi,

I assume that you have one more datefield to compare with CampaignEndDate

then do like this,

Count({<[IsConverted] = {'false'},datefield={">=$(CampaignEndDate)"}>}LeadId)

Regards,

[alexandros17]

Count({<[IsConverted] = {'false'}>} If(date > CampaignEndDate, LeadId, 0))

Let me know

[Anonymous]

"IsConverted" is field from LEAD table,

"CampaignEndDate" is from Campaign table,

LEAD table is connected to Campaignmember table and Campaignmember table id connected to Campaign table.

[alexandros17]

If connections are correct even set analysis is.

Hope it helps

[jonathandienst]

Hi

You can't use set analysis here. The set expression is evaluated once for the table (before the dimensions are built) so QV does not know what value of CampaignEndDate to use.

You have two possible solutions:

     Count({<[IsConverted] = {'false'}>} If (datefield >= CampaignEndDate, LeadId))

But Sum(If()), Count(If()) can be performance killers

OR

modify your load script to bring the CampaignEndDate into the same table as the datefield by the appropriate join. Then set a flag value for dates > CampaignEndDate

     LOAD .....

          If(datefield >= CampaignEndDate, 1, 0) As CampaignEndFlag,

          ....

Now you  can use a set expression

     Count({<[IsConverted] = {'false'}, CampaignEndFlag = {1}>} LeadId)

HTH

Jonathan

[Anonymous]

Thank you, Let me give it a try.

Thanks

Surender