Qlik Community

Qlik Sense Cloud Discussions

Highlighted
im
New Contributor II

Comparing dates in set expression

 

Can someone help me figure out why this doesn't work? I tried every possible variation I could find on forums. Works perfectly when not in set expression.

Year, Month are from Common Calendar and StartDate is a variable in the format 201801

=Count
(distinct{$<Floor(MakeDate(Year,Month))={">=$(=Floor(MakeDate(Left($(StartDate),4),Right($(StartDate),2))))"}, DateType={'Logged'},
}>}ReferenceNumber)

Labels (2)
1 Solution

Accepted Solutions

Re: Comparing dates in set expression

May be this

=Count(DISTINCT {$<
ReferenceNumber = {"=MakeDate(Year,Month) >= MakeDate(Left($(StartDate), 4), Right($(StartDate), 2)) and MakeDate(Year,Month) <= MakeDate(Left($(EndDate), 4), Right($(EndDate), 2))"},
DateType = {'Logged'}
>} ReferenceNumber)
6 Replies
pradosh_thakur
Honored Contributor II

Re: Comparing dates in set expression

count({<column={1,2,3}>}Measure)

In the above example column must be a field , it can't be anything else for example len(field) or date(field).

so you need to do something like this

=Count
(distinct{$< a_dimension_that_you_want_to_restrict = {"=$(=Floor(MakeDate(Year,Month)))>=$(=Floor(MakeDate(Left($(StartDate),4),Right($(StartDate),2))))"}, DateType={'Logged'},
}>}ReferenceNumber)

Learning never stops.
im
New Contributor II

Re: Comparing dates in set expression

Many thanks, now I'm getting zeroes instead of dashes and understand set expressions better, so progress! There's still something wrong with how I use the common calendar.
pradosh_thakur
Honored Contributor II

Re: Comparing dates in set expression

What is the issue now? Is your calendar auto generated. You can always use value instead of expression one by one to debug where you are going wrong.

Let me know incase you need any more help.
Learning never stops.

Re: Comparing dates in set expression

See if this works

=Count(DISTINCT {$<ReferenceNumber = {"=MakeDate(Year,Month) >= MakeDate(Left($(StartDate), 4), Right($(StartDate), 2))"}, DateType = {'Logged'}>} ReferenceNumber)
im
New Contributor II

Re: Comparing dates in set expression

Thank you, that works. How can I add another condition to this code-

<= MakeDate(Left($(EndDate), 4), Right($(EndDate), 2))    ?

Regards 

Re: Comparing dates in set expression

May be this

=Count(DISTINCT {$<
ReferenceNumber = {"=MakeDate(Year,Month) >= MakeDate(Left($(StartDate), 4), Right($(StartDate), 2)) and MakeDate(Year,Month) <= MakeDate(Left($(EndDate), 4), Right($(EndDate), 2))"},
DateType = {'Logged'}
>} ReferenceNumber)
Community Browser