Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Then try this:
=count({<Date={"$(=$(vThisMonth))"},CustID=P({<Date={"$(=$(vLastMonth))"}>}CustID)>}CustID)
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
Thanks Ali
I tried
=count({1}{<Date={"vThisMonth"},CustID=P({<Date={"vLastMonth"}>}CustID)>}CustID)
Result was still 0
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
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
Thanks Peter. Yep, tried that one too. Still result=0
Hi,
try this
=Date#(count({<Date={"=$(=vThisMonth)"},CustID=P({<Date={"=$(=vLastMonth)"}>}CustID)>}CustID))
(or)
=num#(count({<Date={"=$(=vThisMonth)"},CustID=P({<Date={"=$(=vLastMonth)"}>}CustID)>}CustID))
How about?
=count({<Date={"$(=Date(vThisMonth,'DD/MM/YYYY'))"},CustID=P({<Date={"$(=Date(vLastMonth,'DD/MM/YYYY'))"}>}CustID)>}CustID)
Thanks Rakesh, both got more than 0, a lot more. Unfotunately counted every line again (result=40095)
In your document; open Settings->Variable Overview and scroll down until you see the two culprits. What is their current value?
Peter