Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nishanthi_8
Contributor

Set Analysis

count({1<Month_Year=e(Month_Year),[Derived Demand Status]={'Open','Coverage External'}>}[Resource Request Id])

Here it excludes the current month. But I need to exclude the following month also and it is in the format of 'MMM-YYYY'. Suppose for example :

If  I select 'Feb-2016' it should exclude 'Feb-2016' and the following months also. How can it be achieved.

12 Replies
agni_gold
Valued Contributor II

Re: Set Analysis

do one thing ,

Create a variable for selected period

like in same format ,

count({1<Month_Year-={$(v_Variable)},[Derived Demand Status]={'Open','Coverage External'}>}[Resource Request Id])

nishanthi_8
Contributor

Re: Set Analysis

WHATS THE DIFFERENCE?

agni_gold
Valued Contributor II

Re: Set Analysis

please see now my post

nishanthi_8
Contributor

Re: Set Analysis

variable is not a problem it is to exclude the selected and foregoing months

agni_gold
Valued Contributor II

Re: Set Analysis

Are you need to bypass the month selection?

Re: Set Analysis

try this

count({1<Month_Year=e({1<Month_Year={">=$(=date(max(Month_Year),'MMM-YYYY'))<=$(=date(addmonths(max(Month_Year),1),'MMM-YYYY'))"}>}Month_Year),[Derived Demand Status]={'Open','Coverage External'}>}[Resource Request Id])

nishanthi_8
Contributor

Re: Set Analysis

s

MVP
MVP

Re: Set Analysis

You don't need the variable, but you do need a static value and a -= to do the exclude.

Is Month_Year a text field, or is it a numeric QV date field?

I assume that current month can be based on Today().

Something like:

count({1<Month_Year-={"$(=Date(Today(), 'MMM-yyyy'))"},[Derived Demand Status]={'Open','Coverage External'}>}[Resource Request Id])

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
nishanthi_8
Contributor

Re: Set Analysis

It worked thanks a lot... will it work without selection for example if we include the Month_Year in the dimension will the data will be populated automatically?

Community Browser