# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for
Did you mean:  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: 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: 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.

1 Solution

Accepted Solutions  MVP

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)

5 Replies  MVP

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)  Contributor III
Author

However, it does not deliver the desired result yet.

I did insert the new calculated dimension as you proposed. However, if I select a Hoofdgroep (2nd column) in this case Graco Programma, then this table will pop up 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

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
Author

You're right. Thanks all!  MVP

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) 