Grouping data in cyclic dimension group

[#1]

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.

[oleg_orlov]

Hi! You should try Aggr function.

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

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

[#3]

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

[oleg_orlov · Accepted solution]

Try this:

First: Sum (Sale)

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

[#5]

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

[kumarnatarajan]

Hi,

Try this one

Ex: Sum({1}TOTAL Sales)

Find the attached file

[#7]

Hi kumar ,

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

[kumarnatarajan]

Hi,

Try this

Ex: Sum({1}TOTAL Sales)

[#9]

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.

[oleg_orlov]

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).