topic Re: Grouping data in cyclic dimension group in QlikView

Grouping data in cyclic dimension group

Mon, 20 May 2013 06:44:09 GMT

Hi everyone ,

I am new to qlikview and I am facing a problem described below:

I have a cyclic group on dimensions (Category , Sub-Category) . In my chart I have two expression one is sale and other is Total sale.

Sale : Sum(Sale)

Total Sale : Sum({1} Total Sale)

I want following result when I select 'A' and Category is on X-Axis:

Sale(A) : 3

Total Sale : 78

But when Sub-Category is on X-Axis it should group data to sub-category:

Sale(a) : 1

Sale(b) : 2

Total Sale(a) : 36

Total Sale(b) : 42

Here in place of total sale it gives me 78 , may be it is not grouping data to sub - category so how can I do that in my expression?

Category Sub-Category Sale

=========================

A a 1

A b 2

B a 3

B b 4

C a 5

C b 6

D a 7

D b 8

E a 9

E b 10

F a 11

F b 12

==========================

Any help will be highly appreciated.

Re: Grouping data in cyclic dimension group

oleg_orlov — Mon, 20 May 2013 07:07:30 GMT

Hi! You should try Aggr function.

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

Dimension will be name of cyclic group, expression will be Sum.

Re: Grouping data in cyclic dimension group

Mon, 20 May 2013 07:26:52 GMT

Thanks for the reply I have edited expression like this

Aggr(({1} Total Sum(Sale)) ,Sub-Category)

but it gives me 78 , if I remove "{1} Total" from above expression then it gives me 1,2 except 36,42

Re: Grouping data in cyclic dimension group

oleg_orlov — Mon, 20 May 2013 07:31:01 GMT

Try this:

First: Sum (Sale)

Second: Aggr (Sum ({$<Category=, SubCategory=>} TOTAL <SubCategory> Sale), CategoryGroup)

Re: Grouping data in cyclic dimension group

Mon, 20 May 2013 07:38:50 GMT

Thanks a lot Oleg , It worked fine for me now , but can you please elaborate how this works?

Re: Grouping data in cyclic dimension group

kumarnatarajan — Mon, 20 May 2013 07:40:17 GMT

Hi,

Try this one

Ex: Sum({1}TOTAL Sales)

Find the attached file

Re: Grouping data in cyclic dimension group

Mon, 20 May 2013 07:42:04 GMT

Hi kumar ,

I am using trial of Personal edition so I am unable to open this file.

Re: Grouping data in cyclic dimension group

kumarnatarajan — Mon, 20 May 2013 07:43:21 GMT

Hi,

Try this

Ex: Sum({1}TOTAL Sales)

Re: Grouping data in cyclic dimension group

Mon, 20 May 2013 07:46:48 GMT

Hi Kumar ,

I already have used this expression , see my question , but the problem was that it sums whole data it was not grouping data to sub-category , But answer posted by oleg resolved my problem ,

Thanks.

Re: Grouping data in cyclic dimension group

oleg_orlov — Mon, 20 May 2013 12:05:50 GMT

Aggr (Sum ({$<Category=>} TOTAL <SubCategory> Sale), CategoryGroup)

Aggr function works like GROUP BY. So Sum calculates for each value of Category or SubCategory.

QlikView Help for TOTAL: "The total qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimension variables. In this case the calculation will be made disregarding all chart dimension variables except those listed, i.e. one value will be returned for each combination of field values in the listed dimension fields."

And {$<Category=>} means that we reset current selected values for Category. (SubCategory we don't have to reset).

Re: Grouping data in cyclic dimension group

Mon, 20 May 2013 12:22:59 GMT

Thanks