topic Re: Second Set Expression to add second filter in App Development

Second Set Expression to add second filter

Qlik007 — Fri, 14 Feb 2025 22:22:07 GMT

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)

Re: Second Set Expression to add second filter

goncalo_ricardo_pereira — Fri, 14 Feb 2025 23:25:15 GMT

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

Re: Second Set Expression to add second filter

morgankejerhag — Sat, 15 Feb 2025 08:48:15 GMT

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

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

Re: Second Set Expression to add second filter

Chanty4u — Sun, 16 Feb 2025 14:11:17 GMT

Try this

Sum({

<region_name = {'Americas'},

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

>} Dollar_value)

Sum({

<region_name = {'Americas'},

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

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

>} Dollar_value)

Re: Second Set Expression to add second filter

Qlik007 — Sun, 16 Feb 2025 17:29:21 GMT

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 ?

Re: Second Set Expression to add second filter

Qlik007 — Sun, 16 Feb 2025 17:29:55 GMT

Going to try this and update you soon

Re: Second Set Expression to add second filter

Qlik007 — Tue, 18 Feb 2025 14:57:53 GMT

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.

Re: Second Set Expression to add second filter

Qlik007 — Tue, 18 Feb 2025 15:03:02 GMT

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."

Re: Second Set Expression to add second filter

Qlik007 — Tue, 18 Feb 2025 19:50:42 GMT

Tried not getting desired value

Re: Second Set Expression to add second filter

morgankejerhag — Tue, 18 Feb 2025 20:03:37 GMT

Are you making any user selections at the same time?

Re: Second Set Expression to add second filter

BPiotrowski — Wed, 19 Feb 2025 09:24:43 GMT

Hi, @Qlik007

Try this one for prev month, you can also add {1} if you want to ignore user selection.

sum({<Date={"<=$(=MonthEnd(AddMonths(today(),-1))"},region_name= { 'Americas'}>} Dollar_value)