How to give less than condition in set analysis?

[nishanthi_8]

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?

[swuehl]

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

[nishanthi_8]

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

[swuehl]

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

[nishanthi_8]

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

[reddy-s]

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

[swuehl]

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

[sunny_talwar]

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

[nishanthi_8]

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

[sunny_talwar]

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