Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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/
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.
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?
That function returns 0, I guess its not a date?
I added
Date(floor("date"), 'YYYY-MM-DD') AS "date"
to the Data Load Script, and things seem to work now
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)