topic Re: Exclude Dimension in Expression (Pivot table) in QlikView

Exclude Dimension in Expression (Pivot table)

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

Hi Community,

I am stuck in a problem.

i select a pivot chart

consist of 4 dimension

Town, distributor name , date and dsrname

based on this i used expressions

one of expression i used want to exclude DISTRIBUTORNAME which i used as a dimension.

i used this expression but its not working

count(DISTINCT {<[Fiscal Year]=,[Fiscal Month]=,Quarter=,Day=, SALEDATE=, DISTRIBUTORNAME=, REGION=, TOWN=,DSRNAME= >} SHOPCODE)

i want to exclude all dimension from expression.

How can i d this ?

Required help

Regards

Noman khan

Re: Exclude Dimension in Expression (Pivot table)

tresB — Mon, 25 Apr 2016 08:57:15 GMT

Try like:

count(Total DISTINCT {<[Fiscal Year]=,[Fiscal Month]=,Quarter=,Day=, SALEDATE=, REGION=, TOWN=,DSRNAME= >} SHOPCODE)

Update: If you have multiple dimensions in the chart, you might have to add prior dimensions in 'Total' like: Total <Dim1, Dim2>

Re: Exclude Dimension in Expression (Pivot table)

sunny_talwar — Mon, 25 Apr 2016 09:36:59 GMT

Can you elaborate on how it is not working? Is the expression filtering out data based on when you make selections? Would you be able to provide screenshots if providing sample isn't possible?

Re: Exclude Dimension in Expression (Pivot table)

noman212 — Mon, 25 Apr 2016 10:48:39 GMT

Expression:

count(DISTINCT {<[Fiscal Year]=,[Fiscal Month]=,Quarter=,Day=, SALEDATE=,DISTRIBUTORNAME= >} SHOPCODE)

I want Count of shop code Town wise

like Chinnot has a count of 322 total shops

DI Khan has a total of 423 shop and so on...

i exclude Distributor name in expression but it bifurcate total no of shop with "distribution name".

Dimension :

Re: Exclude Dimension in Expression (Pivot table)

sunny_talwar — Mon, 25 Apr 2016 10:54:32 GMT

I think you need this (as tresesco also mentioned)

Count(DISTINCT TOTAL <REGION, TOWN> {<[Fiscal Year]=,[Fiscal Month]=,Quarter=,Day=, SALEDATE=,DISTRIBUTORNAME= >} SHOPCODE)