9 Replies Latest reply: May 27, 2016 5:52 AM by Sunny Talwar

# How to give less than condition in set analysis?

I have a condition to show the count by excluding the current month.  I tried with the following:

count({<MonthYear={'Apr-2016'},[Demand Status]={'Open','Closed'}>}[Request Id])

how to implement that?

• ###### Re: How to give less than condition in set analysis?

To exclude Apr, maybe

count({<MonthYear -= {'Apr-2016'},[Demand Status]={'Open','Closed'}>}[Request Id])

edit:

Or if you want all MonthYear below Mai 2016, maybe

count({<MonthYear = {"<'May-2016'"},[Demand Status]={'Open','Closed'}>}[Request Id])

• ###### Re: How to give less than condition in set analysis?

But i dont want to give any hard coding conditions like excluding... rather than that it should work dynamically

• ###### Re: How to give less than condition in set analysis?

Then create a variable with the current Month, maybe like

vCurrentMonth

=Date(Max({1} MonthYear),'MMM-YYYY')

and use the variable in the set expression

count({<MonthYear -= {'\$(vCurrentMonth)'},[Demand Status]={'Open','Closed'}>}[Request Id])

• ###### Re: How to give less than condition in set analysis?

I need to include '<=' and '>=' condition in set analysis

• ###### Re: How to give less than condition in set analysis?

Hi Nishanthi,

You can use less than equal to or greater than equal to this way in Qliksense:

count( {\$ < [Days In Stock]={">=\$(varLow)<=\$(varHigh)"} >}[Vehicle ID]) / count([Vehicle ID])

• ###### Re: How to give less than condition in set analysis?

Ok, but I already showed the syntax above in the second example:

count({<MonthYear = {">=\$(vStartVariable)<=\$(vEndVariable)"},[Demand Status]={'Open','Closed'}>}[Request Id])

Just create the two variables that show a correct format:

Dates in Set Analysis

A Primer on Set Analysis

• ###### Re: How to give less than condition in set analysis?

Instead of using MonthYear field, would you be able to use a Date field to do all the manipulations?

Count({<DateField = {"\$(='<' & Date(MonthStart(Max(DateField)) 'DateFieldFormatHere'))"}, MonthYear, Year, Quarter, QuarterYear, [Demand Status] = {'Open', 'Closed'}>} [Request Id])

or to just get the last month:

Count({<DateField = {"\$(='>=' & Date(MonthStart(Max(DateField), -1) 'DateFieldFormatHere') & '<' & Date(MonthStart(Max(DateField)) 'DateFieldFormatHere'))"}, MonthYear, Year, Quarter, QuarterYear, [Demand Status] = {'Open', 'Closed'}>} [Request Id])

• ###### Re: How to give less than condition in set analysis?

Could you just explain why '&' is included in the set analysis condition ?

• ###### Re: How to give less than condition in set analysis?

It is used within the dollar sign expansion. So this

='>=' & Date(MonthStart(Max(DateField), -1) 'DateFieldFormatHere') & '<' & Date(MonthStart(Max(DateField)) 'DateFieldFormatHere')

will be evaluated to something like this within the set analysis (for example) -> >=04/01/2016<05/15/2016

& is used to concatenate things. You can test this by adding this to a text box object to see what I mean

='>=' & Date(MonthStart(Max(DateField), -1) 'DateFieldFormatHere') & '<' & Date(MonthStart(Max(DateField)) 'DateFieldFormatHere')

NOTE: Make sure to replace DateFieldFormatHere with your date format