topic Re: Greater Than in Set Analysis in QlikView

Greater Than in Set Analysis

nickking — Tue, 22 Sep 2015 14:13:01 GMT

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

Re: Greater Than in Set Analysis

MK_QSL — Tue, 22 Sep 2015 14:19:11 GMT

May be

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

Re: Greater Than in Set Analysis

Gabriel — Tue, 22 Sep 2015 14:19:58 GMT

Hi,

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

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

Re: Greater Than in Set Analysis

nickking — Tue, 22 Sep 2015 14:22:35 GMT

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.

Re: Greater Than in Set Analysis

nickking — Tue, 22 Sep 2015 14:24:17 GMT

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

Re: Greater Than in Set Analysis

jonathandienst — Tue, 22 Sep 2015 14:24:25 GMT

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)

Re: Greater Than in Set Analysis

nickking — Tue, 22 Sep 2015 14:27:51 GMT

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

Re: Greater Than in Set Analysis

nickking — Tue, 22 Sep 2015 14:31:27 GMT

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.

Re: Greater Than in Set Analysis

nickking — Tue, 22 Sep 2015 15:59:07 GMT

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)