Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Table expression ignoring my calculated dimension

Hi all,

I need to get a list of discount % on HOOFDGROEP (main-level) and SUBGROEP (sub-level) per category (group of customers).

I defined the categories in a calculated dimension as following:

=if(aggr(sum({$<Jaar={2017}, Markt={Houtbouw}>}[sales]), Customer)>0 and aggr(sum({$<Jaar={2017}, Markt={Houtbouw}>}[sales]),Customer)<=2500, '(a) 0 t/m 2.500',

if(aggr(sum({$<Jaar={2017}, Markt={Houtbouw}>}[sales),Customer)>2500 and aggr(sum({$<Jaar={2017}, Markt={Houtbouw}>}[sales]),Customer)<=10000, '(b) 2.500,- t/m 10.000,-',

if(aggr(sum({$<Jaar={2017}, Markt={Houtbouw}>}[sales]),Customer)>10000 and aggr(sum({$<Jaar={2017}, Markt={Houtbouw}>}[sales),Customer)<=25000, '(c) 10.000,- t/m 25.000,-',

if(aggr(sum({$<Jaar={2017}, Markt={Houtbouw}>}[sales]),Customer)>25000, '(d) 25.000,- en meer',

if(aggr(sum({$<Jaar={2017}, Markt={Houtbouw}>}[sales),Customer<=0, '(x) 0 of negatieve omzet')))))

This function is allocating our customers, based on the sum(sales), in each category.

For example, this results in the following table:

Qliktable.PNG

To this point, my calculated dimension is working fine, showing the sales (netto-omzet 2017), per Hoofdgroep and Subgroep.

However, if I make an additional selection (in this case: Graco onder...) on Subgroep (sub-level) my calculated dimension is not working anymore. As shown in the figure below:

Qliktable2.PNG

The calculated dimension (categories) are shifting, resulting in different sales number which are not reflecting reality.


Netto-omzet 2017 is defined as: =sum([Sales)

Bruto-omzet 2017 is defined as: =sum([Sales Bruto])

Korting % 2017 is defined as: =(1-(Column(1)/Column(2)))


Can anybody help me how to make this work properly.


Thanks in advance!

1 Solution

Accepted Solutions

May be try this

=Aggr(

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep>}[sales]) <= 0, '(x) 0 of negatieve omzet',

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep>}[sales]) <= 2500, '(a) 0 t/m 2.500',

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep>}[sales]) <= 10000, '(b) 2.500,- t/m 10.000,-',

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep>}[sales) <= 25000, '(c) 10.000,- t/m 25.000,-',


'(d) 25.000,- en meer'))))


, Customer)

View solution in original post

5 Replies

May be try this

=Aggr(

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep>}[sales]) <= 0, '(x) 0 of negatieve omzet',

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep>}[sales]) <= 2500, '(a) 0 t/m 2.500',

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep>}[sales]) <= 10000, '(b) 2.500,- t/m 10.000,-',

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep>}[sales) <= 25000, '(c) 10.000,- t/m 25.000,-',


'(d) 25.000,- en meer'))))


, Customer)

View solution in original post

Contributor III
Contributor III

Thanks for your input Sunny!

However, it does not deliver the desired result yet.

I did insert the new calculated dimension as you proposed.

Qliktable3.PNG

However, if I select a Hoofdgroep (2nd column) in this case Graco Programma, then this table will pop up

Qliktable4.PNG

With different sales numbers, it seems like it is shifting the customers to another category. Based on their sales realized in the specific selected HOOFDGROEP, which in this case is Graco Programma. Same will happen if I make selections on Sub-level (Subgroep)

Hope you can help me! Thanks in advance!

Specialist
Specialist

Hi All. Sunny is right and you can try to ignore all selections by replacing the set modifier $ to 1 if there are another filters

Contributor III
Contributor III

You're right. Thanks all!

May be you need to ignore selection in Hoofdgroep also


=Aggr(

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep, Hoofdgroep >}[sales]) <= 0, '(x) 0 of negatieve omzet',

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep, Hoofdgroep >}[sales]) <= 2500, '(a) 0 t/m 2.500',

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep, Hoofdgroep >}[sales]) <= 10000, '(b) 2.500,- t/m 10.000,-',

If(Sum({$<Jaar={2017}, Markt={Houtbouw}, Subgroep, Hoofdgroep >}[sales) <= 25000, '(c) 10.000,- t/m 25.000,-',


'(d) 25.000,- en meer'))))


, Customer)

or everything

=Aggr(

If(Sum({1<Jaar={2017}, Markt={Houtbouw}>}[sales]) <= 0, '(x) 0 of negatieve omzet',

If(Sum({1<Jaar={2017}, Markt={Houtbouw}>}[sales]) <= 2500, '(a) 0 t/m 2.500',

If(Sum({1<Jaar={2017}, Markt={Houtbouw}>}[sales]) <= 10000, '(b) 2.500,- t/m 10.000,-',

If(Sum({1<Jaar={2017}, Markt={Houtbouw}>}[sales) <= 25000, '(c) 10.000,- t/m 25.000,-',


'(d) 25.000,- en meer'))))


, Customer)