topic Re: Add calculated dimension in QlikView

Add calculated dimension

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

HI Qlikers

I would appreciate some assistance please.

I am trying to add a calculated dimension so that the results of the dimension are filtered by location UK

So, i am adding the Dimension UK but i would like to filter these resluts by Another dimension ERP Location and the Value UK.

the closest i get to something like the required is

({$<[ERP LOCATION]= {'*UK*'}>} [Customer Name])

should i be using AGGR?

I would like to add a calculated dimension for [CUSTOMER NAME] so that only results from the dimension [ERP LOCATION]=UK show

If anybody could help i can apply this throughout my doc.

Thank you in advance.

Re: Add calculated dimension

vinieme12 — Mon, 21 Aug 2017 13:10:18 GMT

Use calculated dimension as below

AGGR( if( WildMatch([ERP LOCATION],'*UK*'),[CUSTOMER NAME]) , [CUSTOMER NAME] )

AGGR( only({<[ERP LOCATION]={'*UK*'} >} [CUSTOMER NAME]) , [CUSTOMER NAME] )

Re: Add calculated dimension

Kushal_Chawda — Mon, 21 Aug 2017 13:10:32 GMT

instead you can use the set analysis in your expression like below as calculated dimension impacts performance and not advisable

sum({$<[ERP LOCATION]= {'*UK*'}>} Sales)

Re: Add calculated dimension

suryaa30 — Mon, 21 Aug 2017 13:10:54 GMT

Use double quotes

Re: Add calculated dimension

sunny_talwar — Mon, 21 Aug 2017 13:16:32 GMT

I second that

Re: Add calculated dimension

srishsum2017 — Mon, 21 Aug 2017 13:33:41 GMT

AGGR(if( WildMatch([ERP LOCATION],'*UK*'),[CUSTOMER NAME]) , [CUSTOMER NAME] )

Re: Add calculated dimension

davyqliks — Mon, 21 Aug 2017 13:44:43 GMT

Thank you Vineeth Pujari

looking good on the syntax from

=AGGR( only({$<[ERP LOCATION]={'*HK*'} >} [CUSTOMER NAME]) , [CUSTOMER NAME] )

But i am only getting one response in the results and this should not be the case. I should be showing all customers where the location is HK/UK

I have ensured that both the Customer and ERP location dimensions are connected by table. Do you have any idea why this would only show one return with a - and not a customer name?

Thank you

Re: Add calculated dimension

davyqliks — Mon, 21 Aug 2017 13:48:21 GMT

THank you for the response

I changed sales to customer name

sum({$<[ERP LOCATION]= {"*UK*"}>} [Customer Name])

And also tried with double quotes but in the table i see error in calculated dimension.

Any Ideas?

Thank you again for all the knowledge shared guys.

Re: Add calculated dimension

sunny_talwar — Mon, 21 Aug 2017 13:51:36 GMT

This is not used as a dimension... this set analysis expression needs to be used within your expression... then chart where you are using this as a dimension.... what are your expressions?

Re: Add calculated dimension

Kushal_Chawda — Mon, 21 Aug 2017 13:52:43 GMT

If write this expression, you don't need calculated dimension, just use Customer Name as dimension and check the suppress null values option

Re: Add calculated dimension

davyqliks — Mon, 21 Aug 2017 13:55:37 GMT

the calculated dimension i am adding is to filter all results from a table which shows many expressions for sales calculations.

I am hoping the calculated dimension will in turn filter the results of the other expressions

Rank(Sum({1<[Inv Month]={Nov}, [Customer Name] -= {'***Customer name*****''}>}[Invoice Total]),1,1

concat(distinct [Brand Name] & [Retailer Name],', ')

sum({1<[MonthYear]={'Apr-2017'}>}[Invoice Total])

sum({1<[MonthYear]={'May-2016'}>}[Invoice Total])

sum({1<[MonthYear]={'May-2017'}>}[Invoice Total])

sum({1<[MonthYear]={'Jun-2016'}>}[Invoice Total])

=sum({1 <MonthYear={">=Jan-2016<=Jun-2016"}>} [Invoice Total])

=sum({1 <MonthYear={">=Jan-2017<Jun-2017"}>} [Invoice Total])

Will the calculated dimension have the effect i am looking for. I have not used these as of yet?

Thank you

Re: Add calculated dimension

vinieme12 — Mon, 21 Aug 2017 13:58:19 GMT

Try calculated dimension as

AGGR( only({$<[ERP LOCATION]={'HK/UK'} >} [CUSTOMER NAME]) , [CUSTOMER NAME] )

or Add the SET ANALYSIS part to all expressions that you have in the chart'

this part

{$<[ERP LOCATION]={'HK/UK'} >}

Re: Add calculated dimension

davyqliks — Mon, 21 Aug 2017 14:08:38 GMT

Rather than a calculated dimension should i be adding the Location UK syntax to the existing Expressions?

Thank you in advance.

Re: Add calculated dimension

vinieme12 — Mon, 21 Aug 2017 14:30:20 GMT

Yes, that would be the best option

Re: Add calculated dimension

davyqliks — Mon, 21 Aug 2017 14:31:16 GMT

Thank you Vineeth Pujari

I Will work on this option then.

Thank you all for the assistance