Set analysis issues when using date variables

[#1]

Pushing this barrow again.  I have the below set analysis counting like instances under "CustID" over various months.  I have set up variables vThisMonth(01/09/2014) and vLastMonth (01/08/2014) to do pretty much as is stated.

I've used these variables in "where" statements and all working ok.  However, in the below set analysis it just won't recognise the dates.  I've tried the below formats (as per suggestions)

Formula
=count({<Date={"$(=vThisMonth)"},CustID=P({<Date={"$(=vLastMonth)"}>}CustID)>}CustID)
Result
0

Formula
=count({<Date={"$(vThisMonth)"},CustID=P({<Date={"$(vLastMonth)"}>}CustID)>}CustID)
Result
0

Formula
=count({<Date={"=$(=vThisMonth)"},CustID=P({<Date={"=$(=vLastMonth)"}>}CustID)>}CustID)
Result
40095 (which happens to be every instance, non-distinct)

Formula
=count({<Date={"=$(=vThisMonth)"},CustID=P({<Date={"=$(=vLastMonth)"}>}CustID)>}CustID)
Result
40095 (which happens to be every instance, non-distinct)

The only way I can get this to work is to manually add the date

Formula
=count({<Date={"01/09/2014"},CustID=P({<Date={"01/08/2014"}>}CustID)>}CustID)
Result
24 (Correct)

Please help!!  This is doing my head in!!  Thanks all

[ali_hijazi]

did you define vLastMonth and vThisMonth preceeded with an equal sign?

since your dates are formatted then do not use the $ in your set analysis

[#3]

Thanks Ali

I tried

=count({1}{<Date={"vThisMonth"},CustID=P({<Date={"vLastMonth"}>}CustID)>}CustID)

Result was still 0

[ali_hijazi]

I suggest that you transform your date fields into numbers

num(DayStart(date_field))

then repeat your expression with $ and see

one more thing you need to define the vLastMonth and vThisMonth with an "=' in the beginning

[Peter_Cammaert]

How about this:

=count({1}{<Date={"$(vThisMonth)"},CustID=P({<Date={"$(vLastMonth)"}>}CustID)>}CustID)

Simple text substitution before the set is calculated. Note that both these variables should contain a correct date, either numeric (e.g. 45678) or formatted ("1/09/2014")

Best,

Peter

[#6]

Thanks Peter.  Yep, tried that one too.  Still result=0

[#7]

Hi,

try this

=Date#(count({<Date={"=$(=vThisMonth)"},CustID=P({<Date={"=$(=vLastMonth)"}>}CustID)>}CustID))

(or)

=num#(count({<Date={"=$(=vThisMonth)"},CustID=P({<Date={"=$(=vLastMonth)"}>}CustID)>}CustID))

[simenkg]

How about?

=count({<Date={"$(=Date(vThisMonth,'DD/MM/YYYY'))"},CustID=P({<Date={"$(=Date(vLastMonth,'DD/MM/YYYY'))"}>}CustID)>}CustID)

[#9]

Thanks Rakesh, both got more than 0, a lot more.  Unfotunately counted every line again (result=40095)

[Peter_Cammaert]

In your document; open Settings->Variable Overview and scroll down until you see the two culprits. What is their current value?

Peter