Using ">" and/or "<" in Set Analysis

[sspe]

Hi,

I'm trying to make a graph that shows the number of prospects created based on a specific marketing campaign. The count it self is not a problem, but my problem is that due to bad application design I can have prospects with a created date that is before the start date of the marketing campaign.

I'd therefore like to filter out the prospects where "prospect_createddate > campaign_startdate", but I'm struggling with this. I have tried to do it using a set definition, but it seems like a set definition can't handle < and > (but e.g. = is ok). Is there are way to get around this - or are the any other ways I can do this?

Regards

Steen

[#2]

Can you please show an example of your data model?  As a small pill I'll have to say you can put < and > in set analysis but must be constant values. e.g:    

              DateDimension ={"<=constant_date_value>=constant_date_value"}

[salto · Accepted solution]

Hi Steen,

Maybe this could help you. It may not be exactly what you need but can be near from that:

=count({$<[Prospect Created Date] = {">$(=date(Campaign Start Date))"}>}  ID)

Regards.

[#4]

you can do like this : sum({<date={">20130101"}>}sale).

the text in double quotes is a search text which search values > 20130101 in the field 'date'.

Wish it help

Zhou

[swuehl]

If there is only 1 campaign date possible, e.g. selected, try

Count( {< prospect_createddate ={">$(=only(campaign_startdate))"} >} Distinct Prospect)

[sspe]

Thanks for all your quick replies...:-).

I might have come closer, but it's still not working.

My expression looks like this -

COUNT({$<[ContactCreatedDate_Key] ={">ContactSourceCampaignStartingDate_Key"}>} contact_CustomerNumber)

This expression doesn't give me any records eventhough I have records in my selection where ContactCreatedDate_Key > ContactSOurceCampaignStartingDate_Key. I have added a tableBox to show my records and here I have records that fullfill the criteria.

I have then tried to hard-code a Date value into the expression like below and now I get some records

=COUNT({$<[ContactCreatedDate_Key] ={">20130301"}>} contact_CustomerNumber)

I'm still new to QlikView and stll not 100 % familiar with the syntax, so maybe I'm missing something? The Date values I'm,  using it Date_Keys as they come from our datawarehouse, so it's INT values in case that makes any difference?

Regards

Steen

[#7]

Count( {< prospect_createddate ={">$(campaign_startdate"} >}  Prospect)

[#8]

make sure that the field 'contactsourcecampaignstartingdate_key' has only 1 value when calculating your expression or qv can't calculate. you must let qv know: contactsourcecampaignstartingdate_key represent which value. if there are 2 or more, qv can't calculte.
as you can see, your hard-code expression works. that is because 20130301 is the only one value.

thanks

zhou

[sspe]

Hi Hannan,

When I do it like you suggest, I get a graph showing a count of all records in my selection.

My expression now looks like this -

COUNT({<ContactCreatedDate_Key ={">$(ContactSourceCampaignStartingDate_Key)"}>} contact_CustomerNumber)

Just to make it easier, I have narrowed down my selections  so it only contains 6 prospects for a given Campaign. Out of these 6, only 2 of them has a created date that is after the campaign starting date, but the above expression actually includes all 6 of them in the count.

I'm also a little unsure about the purpose of the "$" in the above? If I remove it, I don't get any records at all.

Regards

Steen

[sspe]

Well, it only have one value per prospect and in the records I'm looking at (which is for one campaign) it will of course be the same value for all prospects in the list.

Each prospect can only have one "source campaign" hence only only one source campaign created date and it's this date I'm trying to compare to the prospects created date. So for one prospect there can only be one of each date - but of course there can be many different dates for a greater selection of prospects.