Set Analysis/Sum IF

[sbousfield]

Hi all,
I'm new to this platform, and I'm trying to create a KPI that displays the number of individual countries with greater than 200 cases since a specific date (this is case data of a particular disease).

I've tried to use set analysis to filter the data by country and the date greater than, however the set expression for date does absolutely nothing.

Trying to do simply the sum of cases "sum({<date={'>2020-05-01'}>*<country={'Australia'}>}new_cases)" results in 0, which is the incorrect answer, and even when the answer is not zero, the date part has not effect.

I would really appreciate help.

Thanks in advance

[vinieme12]

1) specify multiple criteria's separated by a comma (,)

2) Ensure the datevalue format matches the format of the "date" field

2020-05-01 must match the date values in "date" field

sum({<date={">2020-05-01"},country={'Australia'}>} new_cases)

refer below

https://community.qlik.com/t5/Design/Dates-in-Set-Analysis/ba-p/1472511

https://www.datameer.com/blog/greater-than-less-than-and-dates-in-qlik-set-analysis/

[sbousfield]

According to the Data Manager, the date format is YYYY-MM-DD.
sum(new_cases) returns "203.5M" (the correct value)

The earliest date is 2020-01-01. sum({<date={">=2020-01-01"}>}new_cases) returns "0".

Trying to use the date set analysis seems to be creating issues.

[vinieme12]

is your datefield actually DATE ?

trying doing =isnum(yourdatefield);  if it returns -1, then the field is formatted correctly as dates

Can you also post a snapshot of your datefield?

[sbousfield]

That function returns 0, I guess its not a date?

[sbousfield]

I added

Date(floor("date"), 'YYYY-MM-DD') AS "date"

to the Data Load Script, and things seem to work now

[vinieme12 · Accepted solution]

while loading the field in data load editor

try below

Date(Date#(Trim(cases.date),'YYYY-MM-DD')) as cases.date

also are you using the  correct field name? can you post a screenshot of the expression editor?

sum({<cases.date={">2020-05-01"},country={'Australia'}>} new_cases)