topic Re: Table expression ignoring my calculated dimension in QlikView

Table expression ignoring my calculated dimension

thymenkristen — Thu, 01 Nov 2018 15:02:43 GMT

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.

Thanks in advance!

Re: Table expression ignoring my calculated dimension

sunny_talwar — Thu, 01 Nov 2018 15:56:12 GMT

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)

Re: Table expression ignoring my calculated dimension

thymenkristen — Thu, 01 Nov 2018 16:10:39 GMT

Thanks for your input Sunny!

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!

Re: Table expression ignoring my calculated dimension

andrey_krylov — Thu, 01 Nov 2018 16:17:44 GMT

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

Re: Table expression ignoring my calculated dimension

thymenkristen — Thu, 01 Nov 2018 16:24:41 GMT

You're right. Thanks all!

Re: Table expression ignoring my calculated dimension

sunny_talwar — Thu, 01 Nov 2018 17:25:55 GMT

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)

Re: Table expression ignoring my calculated dimension

StacyCui — Fri, 02 Dec 2022 14:31:03 GMT

Hi, I met the same issue, I'd like to create a dimension for 3 types according to CY-PY OR Diff. But it only shows the customer who have OR within the month selected. How to show all the customers?

= aggr( if(Sum({1<FY={'CY'},FROZENDATE= {"<=$(=max(FROZENDATE))"},Month>} ORVALUE)-
Sum({1<FY={'PY'},FROZENDATE= {"<=$(=addyears(max(FROZENDATE),-1))"},Month>} TOVALUE)>0 ,'With OR Growth',
if((Sum({1<FY={'CY'},FROZENDATE= {"<=$(=max(FROZENDATE))"},Month>} ORVALUE)-
Sum({1<FY={'PY'},FROZENDATE= {"<=$(=addyears(max(FROZENDATE),-1))"},Month>} TOVALUE)<0
and Sum({1<FY={'CY'},FROZENDATE= {"<=$(=max(FROZENDATE))"},Month>} ORVALUE) <>0 ) ,'With OR Decrease',
if((Sum({1<FY={'CY'},FROZENDATE= {"<=$(=max(FROZENDATE))"},Month>} ORVALUE))=0 ,'CY Order=0'))), customerID)