Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rglenn0780
Contributor

Combining two set modifiers correctly.

Hello everyone,

I am attempting to combine a couple different set modifiers. I am having trouble bringing them both together to work. Currently, we have data to support the months of September and October. I would like to create a set modifier that also removes certain Product Lines. I am currently using:

sum({$<[wProduct Line]=-{"Apples","Oranges"}>} wRevenue)

This is for the product line exclusions.

I am also using

sum({<wMonth={"$(=MaxString(Date#(Month,'MMM')))"}>}wRevenue)

This is so the Revenue defaults to view the latest month.

How can I combine both of these in order to see the latest month without filters and the product lines excluding the ones above.

Thanks so much,

Tags (1)
8 Replies
OmarBenSalem
Esteemed Contributor

Re: Combining two set modifiers correctly.

Hve u tried :

sum({<wMonth={"$(=MaxString(Date#(Month,'MMM')))"} , [wProduct Line]-={"Apples","Oranges"}>}wRevenue)

rglenn0780
Contributor

Re: Combining two set modifiers correctly.

Thanks Omar, you are always saving my day...I am looking into if this works. Say, do you know if this would have any impact on totally excluding/hiding it? we are trying to list bottom few and top few product lines and sometimes when we fix the number to say 5, we get less product lines appearing. And by less, I mean we fix the number at 5 and only 3 appear - it is strange.... Do you know if this equation or the previous ones has an impact on that?

Thanks for your time.

Re: Combining two set modifiers correctly.

if you limit to bottom 5, you may have some rows that have zero values and thus won't appear by default. If you want them to appear uncheck "suppress zero values" on the Presentation tab.

Also note the difference between what you posted:

[wProduct Line]=-{"Apples","Oranges"}


and what Omar posted:

[wProduct Line]-={"Apples","Oranges"}


Yours is "not equal to" and Omar's is "remove these from the selections".  They will return the same results if no selections in wProductLine, but different results if selections are made.


-Rob

http://masterssummit.com

http://qlikviewcookbook.com


OmarBenSalem
Esteemed Contributor

Re: Combining two set modifiers correctly.

Glad to help !

When you want to show top 5 for example; and make some selections; and get only 3; It's normal ! and It depends on what you've selected.

I mean, maybe, with your filter, there is only 3 of ur dimension associated with your selections; so the top 5 thing will logically return this associated 3 values.

It's getting late for me; It's 23:30 in my local Time ..

Sorry if I do not reply..

OmarBenSalem
Esteemed Contributor

Re: Combining two set modifiers correctly.

  Good point ! My bad; u should use [wProduct Line]=-{"Apples","Oranges"}

Re: Combining two set modifiers correctly.

Omar, your example is not necessarily wrong. The OP may have not been sure what they wanted. It's a common mistake people make.

-Rob

Re: Combining two set modifiers correctly.

If I may add my two cents...

I also have a problem with the Month filter. Using MaxString, you will be comparing the Month to the highest available month alphabetically. So, even if it works by accident now, it will eventually break when you load more months of data. I believe you will get better results from comparing Month/Year values, preferably as numbers and not strings...

cheers,

Oleg Troyansky

Check out my book QlikView Your Business - The Expert Guide for QlikView and Qlik Sense

rglenn0780
Contributor

Re: Combining two set modifiers correctly.

Omar,

sum({<wMonth={"$(=MaxString(Date#(wMonth,'MMM')))"} , [wProduct Line]=-{"Apple","Oranges"}>} wRevenue)

Is working correctly except we cannot see 0 value product lines in our bottom 5 revenue analysis. We would like to see these product lines. If we omit the MaxString section -  sum({<wMonth={"$(=MaxString(Date#(wMonth,'MMM')))"}

We are able to see the $0 Product Lines.

Any input on fixing this issue would be greatly appreciated.

Thanks!

Community Browser