Greater Than in Set Analysis

[nickking]

Hi,

I'm trying to sum a field where it's week paid date is greater than the 24/08/2015 using Set Analysis where the date of the 24/08/2015 is calculated by a variable. When I view the variable result in a text box is displays as 24/08/2015. When I use that variable in Set Analysis I get no answer to the calculation. If I just type the date in manually it works.

This is how it looks with the date manually typed

=Sum({1<WeekPaid={">=24/08/2015"}>}treatment_count)

This is my latest attempt at getting that to work with the variable

=Sum({1<WeekPaid={">=$(vFour_Weeks_Ago)"}>} treatment_count)

=$(vFour_Weeks_Ago) returns 24/08/2015

Where am I going wrong?

Thanks

Nick

[MK_QSL]

May be

=Sum({1<WeekPaid={">=$(Date(vFour_Weeks_Ago))"}>} treatment_count)

[Gabriel]

Hi,

You need date function after the $ (dollar sign expansion)

=Sum({1<WeekPaid={">=$(=Date(vFour_Weeks_Ago))"}>} treatment_count)

[nickking]

Thanks Manish but that hasn't solved the problem. Putting that in is bringing back the sum of everything in the dashboard so it's now ignoring the modifier.

[nickking]

Thanks Gabriel. Unfortunately when I try that the result of the formula becomes 0 rather than the 37,000 I'm looking for.

[jonathandienst]

Because the variable is formatted as a date, you will need single quotes (so its not interpreted as 24 divided by 8 divided by 2015):

=Sum({1<WeekPaid={">='$(vFour_Weeks_Ago)'"}>} treatment_count)

[nickking]

Sorry Jonathan that doesn't appear to work either but thanks for replying

[nickking]

Actually making a slight amendment to Gabriel's answer appears to have solved this.

=Sum({1<WeekPaid={">=$(=$(vFour_Weeks_Ago))"}>} treatment_count)

is giving the right answer.

[nickking]

After viewing the Load Script editor I exited without reloading and the formula was no longer working. Currently the formula below is working but as there is obviously a bug in the software I don't know how long the solution will hold.

=sum({1<WeekPaid={".=$(vFour_Weeks_Ago)"}>} treatment_count)