topic Re: Adding a calculated field to a dimension in QlikView

Adding a calculated field to a dimension

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

Hi Everybody

I have this data-set where I have some dimensions. Within one of those dimensions I want add a calculated field, based on two fields in that dimension.

Please se attached example. Trying to do it with Left Join and Resident in the script function.

Is it possible in any way?

Regards

Stefan

Re: Adding a calculated field to a dimension

Anonymous — Mon, 22 May 2017 12:58:45 GMT

So basically I want to add another field under the dimension "Type", which is called "Rev per page".

Re: Adding a calculated field to a dimension

Kushal_Chawda — Mon, 22 May 2017 13:37:45 GMT

Create synthetics dimension using valuelist function or create inline table

Data:

LOAD Media,

Type,

Month,

Amount

FROM

C:/example.xlsx

(ooxml, embedded labels, Table is Ark1);

Type:

load * inline [

TypeNew

Pages

Revenue

Rev per page

];

Create chart

Dimension:

TypeNew

Expression:

Pick(match(TypeNew,'Pages','Revenue','Rev per page'),

sum({<Type={'Pages'}>}Amount),

sum({<Type={'Revenue'}>}Amount),

sum({<Type={'Pages'}>}Amount)/

sum({<Type={'Revenue'}>}Amount) )

Re: Adding a calculated field to a dimension

Anonymous — Tue, 23 May 2017 06:24:46 GMT

Hi Kushal

Either I'm doing something wrong or it's not working.

When I'm Copying above script into my qvd file, it doesn't give me the right answer.

It just says "Error in calculation" and just Sum alle the numbers.

Regards

Stefan

Re: Adding a calculated field to a dimension

Anonymous — Tue, 23 May 2017 10:39:49 GMT

I have been trying to figure what I'm doing wrong, but I can't seem figure it out.

Anyone else got some ideas how to solve this?

Regards

Stefan

Re: Adding a calculated field to a dimension

qlikview979 — Tue, 23 May 2017 10:44:55 GMT

Can you please share sample app.

Re: Adding a calculated field to a dimension

Anonymous — Tue, 23 May 2017 10:48:20 GMT

Hej Mahesh

Please see attached

Regards

Stefan

Re: Adding a calculated field to a dimension

qlikview979 — Tue, 23 May 2017 10:50:53 GMT

can you please share the sample source file.

Re: Adding a calculated field to a dimension

Kushal_Chawda — Tue, 23 May 2017 11:04:13 GMT

try this

Dimension:

=ValueList('Pages','Revenue','Rev per page')

Expression:

=Pick(match(ValueList('Pages','Revenue','Rev per page'),'Pages','Revenue','Rev per page'),

sum({<Type={'Pages'}>}Amount),

sum({<Type={'Revenue'}>}Amount),

sum({<Type={'Pages'}>}Amount)/

sum({<Type={'Revenue'}>}Amount))

Re: Adding a calculated field to a dimension

Anonymous — Tue, 23 May 2017 11:39:54 GMT

Yes of course

Re: Adding a calculated field to a dimension

Kushal_Chawda — Tue, 23 May 2017 11:57:11 GMT

Have you tried this? It's working for me

Re: Adding a calculated field to a dimension

Anonymous — Tue, 23 May 2017 12:09:35 GMT

That worked exactly like I wanted too

Thanks a lot. You just saved me a lot of time.

Regards

Stefan

Re: Adding a calculated field to a dimension

Kushal_Chawda — Tue, 23 May 2017 13:02:46 GMT

Awesome