Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have below sample data. I want count entity with date greater than today and show country wise.
country | entity | date |
count1 | a | 3/18/2016 |
count2 | b | 7/18/2016 |
count1 | c | 5/18/2016 |
count1 | d | 9/18/2016 |
count2 | e | 5/18/2016 |
count2 | f | 2/18/2016 |
count1 | g | 5/18/2016 |
count1 | h | 1/18/2016 |
count2 | i | 8/18/2016 |
Output
count1 3
count2 2
You can either do this using a flag created in the script:
Table:
LOAD country,
entity,
date,
If(date >= Today(), 1, 0) as Flag
FROM
[https://community.qlik.com/thread/217313]
(html, codepage is 1252, embedded labels, table is @1);
Expression:
=Count({<Flag = {1}>}entity)
or you can try this expression
=Count({<date = {"$(='>=' & Date(Today()))"}>}entity)
Hi ,
try like below in expression,
=aggr(count({<date={">$(=date(today(),'MM/dd/yyyy'))"}>}entity),country)
You can either do this using a flag created in the script:
Table:
LOAD country,
entity,
date,
If(date >= Today(), 1, 0) as Flag
FROM
[https://community.qlik.com/thread/217313]
(html, codepage is 1252, embedded labels, table is @1);
Expression:
=Count({<Flag = {1}>}entity)
or you can try this expression
=Count({<date = {"$(='>=' & Date(Today()))"}>}entity)
use a straight table with Dimension country
and Expression: if (date> today(), Count(Country))
if you need to prepare in script:
NewTable:
load Country,
Count(Country) as counter
resident table
where date > today()
Group by country
Create Straight Table
Dimension:
Country
Expression:
=Count({<entity ={"=Date>today()"}>}entity )
How are you getting a count of 2 for count2?