Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Dhanashree
Partner - Contributor II
Partner - Contributor II

Need to filter data based upon Condition

Hi All,

I have one column which contains date in 'DD/MM/YYYY' format. That column data type is 'String'.

I want to take count of records where year of date >= 2017.

How to write expression in measure?

Can someone please help to resolve it

Thanks,

Dhanashree

2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

That's tricky with string dates. If your data set is small, you can use Sum(If()):

Count(Distinct If(Right(DateS, 4) >= 2017, IDS))

But this may perform poorly in a large data model. Replace DateS and IDS with valid fieldnames.

The better approach is to convert the date string field to a proper date type (which is a numeric value formatted as a date) during load:

LOAD ...
Date(Date#(DateS, 'DD/MM/YYYY')) as Date,
Year(Date#(DateS, 'DD/MM/YYYY')) as Year,
...

Then your expression would be:
Count({<Year = {">=2017"}>} Distinct IDS)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Dhanashree
Partner - Contributor II
Partner - Contributor II
Author

Thanks Jonty 🙂 It's working