topic Set Analysis with yearstart and yearend dates not working in QlikView

Set Analysis with yearstart and yearend dates not working

chrismtb — Wed, 27 Jan 2021 11:59:18 GMT

Hi Qlik friends,

I have an expression that I am trying to use but keeps returning 0 (although i know data exists for the parameters. I have searched this forum and have used the syntax as per other examples but it will not work for me... can someone put me out of my misery by telling me what i am doing wrong!!

my expression is this:

=count(distinct{<ActivityTier={'T0'},Rag={'GREEN'},ScheduledFinishDate={"$(= '>=' & YearStart(today()) & '<=' & YearEnd(today()))"}>}[MilestoneId])

I have tried with my ScheduledFinishDate loaded as is from the source and also loaded as a date in the script and neither seem to make any difference.

Thanks

Chris

Re: Set Analysis with yearstart and yearend dates not working

PradeepK — Wed, 27 Jan 2021 13:45:36 GMT

Hi Chris,

Not sure where you got that example from... Use below code for reference.

Code Sample:

Sum({<Date = {">=$(=YearStart(Today()))<=$(=YearEnd(Today()))"}>}Sales)

Result:

Reference Points :

Set Analysis - Modifier assignment
Numbers = {1}
Strings = {'This is for string assignment'}
Wild Cards = {"*?>=<"}
Debug your code using response from expression debugger.

Hope this helps.

Regards,

Pradeep

Re: Set Analysis with yearstart and yearend dates not working

chrismtb — Wed, 27 Jan 2021 14:27:34 GMT

Thanks for the reply Pradeep.

The code sample I posted was actually my own, adjusted from what I had seen on the forum questions.

I have tried paring back the complexity (number of criteria I am checking against) and using the code sample you provided (with my dimensions) however I am still getting zeros.

I have tried both with Sum and Count functions as per the below and all still return zero values.

I have tried:

=Count({<ScheduledFinishDate = {">=$(=YearStart(Today()))<=$(=YearEnd(Today()))"}>}[MilestoneId])

=Sum({<ScheduledFinishDate = {">=$(=YearStart(Today()))<=$(=YearEnd(Today()))"}>}[MilestoneId])

I don't expect the bottom to show correctly as I am trying to count the number of distinct milestones that hit a set of criteria (rather than adding together the values numeric IDs).