Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
nickking
New Contributor III

Greater Than in Set Analysis

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

8 Replies
MVP
MVP

Re: Greater Than in Set Analysis

May be

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

Partner
Partner

Re: Greater Than in Set Analysis

Hi,

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

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

nickking
New Contributor III

Re: Greater Than in Set Analysis

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
New Contributor III

Re: Greater Than in Set Analysis

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

MVP
MVP

Re: Greater Than in Set Analysis

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
nickking
New Contributor III

Re: Greater Than in Set Analysis

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

nickking
New Contributor III

Re: Greater Than in Set Analysis

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
New Contributor III

Re: Greater Than in Set Analysis

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)