Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
chrismtb
Creator
Creator

Set Analysis with yearstart and yearend dates not working

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

2 Replies
PradeepK
Creator II
Creator II

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: 

Set Analysis Dates.png

 

Reference Points :

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

     

Hope this helps.

Regards,

Pradeep

 

chrismtb
Creator
Creator
Author

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).