topic Re: How to add date filter in expression in QlikView

How to add date filter in expression

Anonymous — Mon, 12 May 2014 14:58:41 GMT

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.

Re: How to add date filter in expression

PrashantSangle — Mon, 12 May 2014 15:03:24 GMT

Hi,

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

then do like this,

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

Regards,

Re: How to add date filter in expression

alexandros17 — Mon, 12 May 2014 15:04:39 GMT

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

Let me know

Re: How to add date filter in expression

Anonymous — Mon, 12 May 2014 15:11:17 GMT

"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.

Re: How to add date filter in expression

alexandros17 — Mon, 12 May 2014 15:16:32 GMT

If connections are correct even set analysis is.

Hope it helps

Re: How to add date filter in expression

jonathandienst — Mon, 12 May 2014 15:24:36 GMT

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

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

Re: How to add date filter in expression

Anonymous — Mon, 12 May 2014 15:46:16 GMT

Thank you, Let me give it a try.

Thanks

Surender