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

[#2]

unfortunately back to 0 with this one.  Thanks Simen

[simenkg]

Then this is a Data-problem, and not a Set analysis problem.

That expressions does exactly what you ask when I test it myself.

How do you define your variables?

[#4]

vThisMonth = 01/09/2014

vLastMonth = 01/08/2014

[#5]

SET vThisMonth = MakeDate(2014, NUM(Month(Today())-1,'00'),01); //Report Month

SET vLastMonth = MakeDate(2014, NUM(Month(Today())-2,'00'),01); //Previous Report Month

[simenkg]

try changing it to

let vThisMonth = MonthStart(Today()-1);

let vLastMonth = MonthStart(Addmonths(Today()-1,-1));

By using LET, the variable will have a value when you reload the script.

If this does not work i would use

let vThisMonth = Floor(MonthStart(Today()-1)));

let vLastMonth = Floor(MonthStart(Addmonths(Today()-1,-1)));

If that doesnt work, then would you mind pasting a value from your Date field? Do you perhaps use a timestamp in your Date field?

[jpenuliar]

I agree with Ali,

May I suggests also to try showing your Variables in text box to compare your actual variable value to what is really your expected value from your Table.This is how i usually debug my formulas.

Regards,

JP

[#8]

I trid that Simen.  However caused too many problems with other setups, scripts etc.  I had to change back to original.

The above value I entered are the Autogen results.  so variables have those dates in that format (DD/MM/YYYY).

The source date date is as below (from table view preview)

[simenkg · Accepted solution]

Then try this:

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

[#10]

You Simen, are a legend, that worked prefectly!!!!

Thanks mate

To all others, thanks for your help as well.