Excluding a Dimension

[Anonymous]

Dear all,

In qlikview, I have a data base in that way, as below:

Status	Year	Product Category	CBU	Family	Jan	Fev
FCST	CY	Cars	1	L	29	34
FCST	CY	Cars	1	M	67	64
FCST	CY	Cars	1	H	14	22
FCST	CY	Cars	2	L	9	9
FCST	CY	Cars	2	M	7	9
FCST	CY	Cars	2	H	5	7
MKT	CY	Cars		L	1	1
MKT	CY	Cars		M	0	0
MKT	CY	Cars		H	11	21

I have a problem: I'd like to show all MKT volumes when I choose any CBU (1 and/or 2). But MKT appears as a "blank" CBU dimension

What should I do?

Regards

[kfoudhaily]

in your sample data it seams to be no CBU when status=MKT

so it seams to be normal if the rest of your data is like this.

you would like to do the exlcution in script while importing data or when visualizing it?

[Anonymous]

Hi,

I'd like to exclude only in MKT while visualizing it.

Thanks for the fast response

[kfoudhaily]

in set analysis, if you are calculating let say the sum for "Jan"

then you should do

sum({$<Status-={'MKT'}>}Jan)

please note the "-=" to exclude

or even more:

sum({$<Status-={'MKT'}>}Jan) + sum({$<Status-={'MKT'}>}Fev)

do you want to make it independent from selections in CBU too?

[Anonymous]

Yes.

I want to show the same amout when, for example, I choose: only CBU1 or CBU 2 or both.

Thanks.

[kfoudhaily]

try this:

sum({$<CBU=,Status-={'MKT'}>}Jan) + sum({$<CBU=,Status-={'MKT'}>}Fev)

you can also check this link for more info:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/SetAnalys...

Best Regards,

---------------------------------------------

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

[Anonymous]

My mistake. I think its not clear.

I want to show MKT amount's but exluding any filter from CBU.

thanks again

[venelin_rs]

Hi Filippo,

If I understand correctly you want to ignore CBU, so use the following expression:

sum({$<CBU= >}Jan)

If it is not working that, you can fill in all blank values with "mapping" function. Later you can play with this new value in your formula as "Set Analysis"

[Anonymous]

Exactly.

But I want to exclude only in MKT filter. I want to show at the same time in a bar chart: mkt excluding cbu and fcst including cbu.

thanks

[venelin_rs]

Why you don't try with 2 different expressions:

     sum({$<Status={'MKT'} , CBU= >}Jan) and sum({$<Status={'fcst '} , CBU={1,2} >}Jan)

Something like that.