Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nishanthi_8
Contributor

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?

9 Replies
MVP
MVP

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

nishanthi_8
Contributor

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

MVP
MVP

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

nishanthi_8
Contributor

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

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

reddys310
Honored Contributor II

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

MVP
MVP

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

MVP
MVP

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

nishanthi_8
Contributor

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

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

MVP
MVP

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

Community Browser