Qlik Community

QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

Highlighted
darrihilmarsson
New Contributor III

How to make a calculated dimension the way I want?

Hi, QV community

I need to some help to make a calculated dimension.

I have an example for you guys to see.

I will talk about the example because you understand better if you look at it.

First take a look at the example before read my explanation.

One Job Number (AARS10) which is the same number as the console Job has information about the cost and Container Type.

The other Job Numbers (AARS11 and AARS12) have information about the revenues and nothing about the container type as you can see in the data below

I need the to get the same Container Types value in all my jobs. So when I filter Container Type "40HC" I will get jobs number AARS10, AARS11, AARS12 and IMMS10.

I tried to use this formula below but I did not work the way I want it but it is close.

=aggr(MaxString(TOTAL <[Console Job]> [Container Type]),[Console Job],[Job number])

It worked but created a problem for Console Job IMMS10 becuase it has many different Container Types.

How can I do make an formula in the calculated dimension that shows the way I want.

You can see how I want it in the Calculated dimension below.

Any ideas?


Console JobJob numberRevenueCostContainer

Container

Type

Calculated dimension
AARS10AARS1003500340HC40HC
AARS10AARS11150000-40HC
AARS10AARS12250000-40HC
IMMS10IMMS1050003000140HC40HC
IMMS10IMMS1050003000120DC20DC
IMMS10IMMS1050003000140DC40DC
Tags (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: How to make a calculated dimension the way I want?

Try =aggr(if([Container Type]<>'-'   ,[Container Type]  ,MaxString(total <[Console Job]> [Container Type])), [Console Job], [Job number],[Container Type])


talk is cheap, supply exceeds demand
3 Replies
MVP & Luminary
MVP & Luminary

Re: How to make a calculated dimension the way I want?

Try =aggr(if([Container Type]<>'-'   ,[Container Type]  ,MaxString(total <[Console Job]> [Container Type])), [Console Job], [Job number],[Container Type])


talk is cheap, supply exceeds demand
darrihilmarsson
New Contributor III

Re: How to make a calculated dimension the way I want?

thanks for the respond Gysbert

what should I do if the database looked like this below.

The only thing I changed is the container type has no value for Job number AARS11 and AARS12.

What should I change if there is no value just nothing

Console JobJob numberRevenueCostContainer

Container

Type

Calculated dimension
AARS10AARS1003500340HC40HC
AARS10AARS1115000040HC
AARS10AARS1225000040HC
IMMS10IMMS1050003000140HC40HC
IMMS10IMMS1050003000120DC20DC
IMMS10IMMS1050003000140DC40DC
MVP & Luminary
MVP & Luminary

Re: How to make a calculated dimension the way I want?

Try =aggr(if(len(trim([Container Type]))>0', [Container Type], MaxString(total <[Console Job]> [Container Type])), [Console Job], [Job number],[Container Type])


talk is cheap, supply exceeds demand