topic Re: Excluding a Dimension in QlikView

Excluding a Dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Excluding a Dimension

kfoudhaily — Thu, 21 Jun 2018 14:13:36 GMT

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?

Re: Excluding a Dimension

Anonymous — Thu, 21 Jun 2018 14:16:47 GMT

Hi,

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

Thanks for the fast response

Re: Excluding a Dimension

kfoudhaily — Thu, 21 Jun 2018 14:21:55 GMT

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?

Re: Excluding a Dimension

Anonymous — Thu, 21 Jun 2018 14:24:26 GMT

Yes.

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

Thanks.

Re: Excluding a Dimension

kfoudhaily — Thu, 21 Jun 2018 14:30:03 GMT

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/SetAnalysis/set-analysis-expr…

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.

Re: Excluding a Dimension

Anonymous — Thu, 21 Jun 2018 14:34:52 GMT

My mistake. I think its not clear.

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

thanks again

Re: Excluding a Dimension

venelin_rs — Thu, 21 Jun 2018 14:45:05 GMT

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"

Re: Excluding a Dimension

Anonymous — Thu, 21 Jun 2018 14:48:18 GMT

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

Re: Excluding a Dimension

venelin_rs — Thu, 21 Jun 2018 15:14:01 GMT

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.

Re: Excluding a Dimension

kfoudhaily — Thu, 21 Jun 2018 16:15:35 GMT

so this should be ok:

sum({$<CBU= >}Jan)

whatever value from CBU calculate sum