Set Analysis when data is a variable

[#1]

I have used set analysis to define the below variable called Purchase

sum

({$<Reregistration={No},[Switch In]={No}>}PurchaseValue)

and this works fine but what i want to do now is use set analysis to give me the Month to date figure so i have done the below and it works when the data i want to SUM is a data field but in this instance i can't use the data field as i have had to create the variable (called Purchase above)

So i have typed in vPurchase at the end - example below and tried different variations on this but i can't get it to work

sum({<Year=,Month=,DateCompleted = {"<=$(=date(max(DateCompleted)))>=$(=date(max(yearstart(DateCompleted))))"}>}(vPurchase))

Please help!!!!

[jedgson]

If vPurchase is the variable name then replace this with $(vPurchase)

[#3]

Is vPurchase the Sum expression from above? If so, your problem is a sum inside a sum. You can't do that unless you utilize the Aggr() function.

Do you really need the sum inside the sum or are you just looking to include all of that Set Analysis into one expression and using the variable to simplify things? You can set a variables value to be part of your Set Analysis and then use it. Set the value of vPurchase to be:

Reregistration={No},[Switch In]={No}

Then use the variable inside your second Set expression:

sum({<Year=,Month=,
 DateCompleted = {"<=$(=date(max(DateCompleted)))
 >=$(=date(max(yearstart(DateCompleted))))"},
 $(vPurchase)>}PurchaseValue)

[#4]

Thanks for this, i have created the variable you suggested and called it vpurcahse2 and put it in the below expression and the table is now giving me values but only for the selected date ie the month to date bit isn't working. Can you see anything wrong with the below?

sum({<Month=,DateCompleted = {"<=$(=date(max(DateCompleted)))>=$(=date(max(Monthstart(DateCompleted))))"},(vpurcahse2)>}PurchaseValue)

It is very annoying because when you hover over the top of the table as i haven't named it shows me the selection and the expression is saying 16/04/2010 = 01/04/2010 but is giving me the value of the 16th??!11

[#5]

Are your date formats correct? Your Set Modifier on DateCompleted should override the selection.

Try it as this:

sum({<Month=,DateCompleted = {"<=16/04/2010>=01/04/2010"},(vpurcahse2)>}PurchaseValue)

If your date format is not correct, you will not be overriding the date selection and you will get values for only the selected date.

Look at the values for DateCompleted, are they 01/04/2010 or maybe 1/4/2010 or maybe something else?

[#6]

Thanks you very much for your help, i was using the wrong date in the list box... So i now have it working