topic Re: Re: aggregation on calculated dimension in QlikView

aggregation on calculated dimension

mambi — Sun, 19 Jan 2014 22:23:12 GMT

Hi everyone,

from the table below :

Element A1

Element A2

Element A3

Element A4

Element A5

Element B1

Element B2

Element B3

Element C1

Element C2

Element C3

Element C4

Element D1

Element D2

Element D3

Element D4

Element E1

Element E2

Element E3

i created a first chart using Left(trim(A),Len(trim(A))-1) as a calculated dimension and Count(A) as expression then i get this result

from here i want to create a second chart so i get this result :

5 -> 1 ( 1 element A ),

4 -> 2 ( 2 elements C and D) ,

3 -> 2 ( 2 elements B and E)

i tried aggregation but no results

any idea ?

Re: aggregation on calculated dimension

nagaiank — Sun, 19 Jan 2014 22:54:45 GMT

One way of getting the result using script is attached.

Re: aggregation on calculated dimension

mambi — Sun, 19 Jan 2014 23:04:13 GMT

thanks for your help

But is there another method ( not in the load script ) to get the same result directly on the chart by using aggregation or something else ?

Re: aggregation on calculated dimension

Mon, 20 Jan 2014 04:42:06 GMT

Hi , Add new field to your script called B by using: Left(Trim(A),Len(A)-1)

Then create the Calculated dimension: Aggr(Count(A) ,B)

Expression: Count(Distinct B)

Please find the attached file for reference.

Re: Re: aggregation on calculated dimension

Anonymous — Mon, 20 Jan 2014 05:42:06 GMT

I don't think this is possible without doing it in the script. As far as I am aware of you can not do a calculation in the dimension part of the aggr function.

So,

AGGR( Count(A) , Left(trim(A),Len(trim(A))-1) ) would not work.

Re: Re: aggregation on calculated dimension

jagannalla — Mon, 20 Jan 2014 05:53:32 GMT

Hi,

Please find attachment.

T:

LOAD Left(trim(A),Len(trim(A))-1) as Dim,A INLINE [

    A

    Element A1

    Element A2

    Element A3

    Element A4

    Element A5

    Element B1

    Element B2

    Element B3

    Element C1

    Element C2

    Element C3

    Element C4

    Element D1

    Element D2

    Element D3

    Element D4

    Element E1

    Element E2

    Element E3

];

T1:

LOAD Dim,Count(A) as Val Resident T Group By Dim;

- Now add Dimension as Dim and expr as = aggr(NODISTINCT Count(Val),Val)

Hope it helps you!!!!

Thanks,

Jagan