Second Set Expression to add second filter

[Qlik007]

I have one big tables with order related fields 

in KPI i need to show sales by region 

I am showing sum of product sold by region

Master item  = sum({<region_name= { ‘Americas’}>} Dollar_value)

I  displaying this in graph where X axis is ( YEAR &'-'& Month) 

Y axis - Above master item , with 2 other master item 

now I need to add set expression where it shows all the months data except current month

i dont have full date but i do have columns(attributes) which gives me month and year separate

my month filed name is “Month” and year field is called “Year “

this is what i have tried but obviously it doesn’t work

sum({<

Month={MonthEnd('$(today()),-1')},

region_name= { ‘Americas’}

>} Dollar_value)

[goncalo_ricardo_pereira]

Hi @Qlik007 ,

To make our set analysis much easier, I would create a flag in the script to allow me to build my set analysis like this:

sum({<region_name={'Americas'}, closedMonthsFlag={1}>} Dollar_value)

if the script, I would do something like this:

if(floor(makedate(Year, num(Month),01)) < floor(monthstart(today())), 1, 0) as closedMonthsFlag

Hope it helps

Best Regards

Goncalo Pereira

[morgankejerhag]

Do you have a date field? You can calculate this as

sum({<Date={"<$(=date(monthstart(today()))"},region_name= { ‘Americas’}>} Dollar_value)

[Chanty4u]

Try this 

Sum({

  <region_name = {'Americas'},

   Year = {"<$(=Year(Today()))"}

  >} Dollar_value)

+

Sum({

  <region_name = {'Americas'},

   Year = {"=$(=Year(Today()))"},

   Month = {"<$(=Month(Today()))"}

  >} Dollar_value)

[Qlik007]

I dont have a date field but my QVD does have a "YYYYMM" field and i am parsing that from SQL database as Date# without the "day" Does that help ?

[Qlik007]

Going to try this and update you soon 

[Qlik007]

So i tried this, it did take out current month but it also took out everything before current month. what can i do to keep everything but only remove dollar value for current month. 

[Qlik007]

I tried this and it gives same results as @goncalo_ricardo_pereira suggested. but that its still not the final visual we want 

Quote from previous response "So i tried this, it did take out current month but it also took out everything before current month. what can i do to keep everything but only remove dollar value for current month."

[Qlik007]

Tried not getting desired value 

[morgankejerhag]

Are you making any user selections at the same time?