Combining two set modifiers correctly.

[Anonymous]

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,

[OmarBenSalem]

Hve u tried :

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

[Anonymous]

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.

[rwunderlich]

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]

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]

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

[rwunderlich]

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

[Oleg_Troyansky]

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

[Anonymous]

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!