Count Aggr on Dimension anomaly

[amber2000]

Hello Fellow Qlik Users,

I have a strange problem in my pivot table chart.

I use 3 expressions

1) Expression  #Colli    =Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }[SD_Shipped_Colli])

Calculate the Sum of "SD_Shipped_Colli" based on the current selection

but before calculating:

- Exclude records equal to "12" in field "SD_WH"

- Exclude records equal to "PBS_Containers" and "PBS_Displays" in field Aisle_Zone

2) for the second Expression #Pallets I use a combination of Count and Aggr function:

= Count({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }aggr(count([Aisle_Facturation]), SD_Dest_Pallet))

Count the distinct values of  "SD_Dest_Pallet" based on the current selection

but before Counting:

- Exclude records equal to "12" in field "SD_WH"

- Exclude records equal to "PBS_Containers" and "PBS_Displays" in field Aisle_Zone

- Group by Aisle_Facturation

3) the third expression Colli / Pal Uit is the result out of the divide Expression1 by Expression2

= #Colli / #Pallets

This is presented per month and if I select only one month there is no problem, select up to 6  months no problem BUT if I select more then 6 months the data changes for the expression #Pallets where I use the Aggr function:

When I select 1 month the #Pallets value for jan is correct:

When I select 6 months the #Pallets value for jan is correct:

When I select a whole year (2015) the #Pallets value for jan is changed and not correct anymore.

Does anybody know why my aggregate calculation is done correctly up until 6 months and when I select more months the calculation is going wrong. (The dataset contains +- 30.000.000 records)

kind regards,

Monique

[sunny_talwar]

This expression doesn't fit the description:

= Count({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }aggr(count([Aisle_Facturation]), SD_Dest_Pallet))

2) for the second Expression #Pallets I use a combination of Count and Aggr function:

= Count({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }aggr(count([Aisle_Facturation]), SD_Dest_Pallet))

Count the distinct values of  "SD_Dest_Pallet" based on the current selection

but before Counting:

- Exclude records equal to "12" in field "SD_WH"

- Exclude records equal to "PBS_Containers" and "PBS_Displays" in field Aisle_Zone

- Group by Aisle_Facturation

Do you need this?

=Count({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}>} Aggr(Count(DISTINCT SD_Dest_Pallet), [Aisle_Facturation]))

[swuehl]

Besides what Sunny mentioned, shouldn't you use Sum() as outer aggregation function?

And the reason why your data changes might be caused by a dimensional grain mismatch:

Pitfalls of the Aggr function

You are using a dimension SD_Dest_Pallet as dimension to aggr() function, but three different dimensions in your chart.

Take care that your grain in the aggr() function is lower than in the chart.

[kamielrajaram]

Hi,

Maybe Try This

Count(aggr(count(DISTINCT {$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> } [Aisle_Facturation]), SD_Dest_Pallet,Other Dimensions))

Regards

Kamiel

[amber2000]

Hello Kamiel

What do you mean with other dimensions?

Can you give an example please?

I've noticed that when I make a single selection of the Aisle_Facturation (for example PBL_OVERIGE) the values for # pallets also changes to a different value.

My theory for that is that when you select an individual dimension value the aggr() purpose is gone and the distinct function is used (that's what I see in the details of the data anyway), is this correct?

Kind regards,

Monique

[swuehl]

Hi Monique,

I vaguely remember a very similar discussion some months ago, were you involved in that?

Have you read the blog post I've linked above or

Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies

I think what you are seeing here is the DISTINCTness of the Aggr() function.

If I remember correctly the past discussion, that's what you explictely want to have, counting each item only once, even when it has multiple relations to the chart dimension values.

But maybe I am totally wrong here, so you might need to shed some light on your complete requirements.

[kamielrajaram]

Hi Monique,

By other dimensions, I meant Year, month, year and month. Also to use the fields in your cyclic group.

Sum(aggr(count(DISTINCT {$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> } [Aisle_Facturation]), SD_Dest_Pallet,Year, Month,[Aisle_Facturation]))

Regards

Kamiel

[amber2000]

Hi Kamiel

I Can't use your statement because then I loose the Distinct even though there is a Distinct in the expression.

Monique

[amber2000]

Hi ,

Yes I was Started that discussion and then I thought the problem was solved.

Sadly I discovered that it wasn't solved completely when I selected a whole year and then one value of the dimension Aisle_Facturation.

Now I've read the post and it's still puzzling me on how to fix my problem (I can't use NODISTINCT to solve this because I need the distinct values)

What I'm trying to achieve is:

A distinct Count of SD_Dest_Pallets over

     -  Aisle_Facturation

     -  Shop_Ens

     -  per Month (can be multiple months up to more than a year)

Additionally when necessary I want to see the the data per SD_Delivery_Date

• One SD_Destination_Pallet can appear in 2 different SD_Delivery_Date (so this is also the case for month, a SD_Destination_Pallet can appear in 2 different months when the date is at the end of the month)
• One SD_Destination_Pallet can appear in different Aisle_Facturation
• A SD_Destination_Pallet can't appear in different Shop_Ens

It was indeed what I asked but when I select a Year the data shouldn't change per month for #pallets and Colli/Pal Uit

I can understand that I loose the distinct when I select one value of Aisle_Facturation but not the change in values per month if I select more then 6 months.

Is it wright when I think of Aggr() as a kind of Group By?

Kind regards,

Monique

[amber2000]

Hi Kamiel,

I'm so sorry that I keep bothering you all about this issue but it's very important and I need to present my results thursday and it doesn't work.

I'm getting so frustrated with the aggr() but I'm almost there.

This is now my expression:

Sum(aggr(Count({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> } DISTINCT SD_Dest_Pallet), Year, MonthYear, Month, WeekDay, SD_Delivery_Date, Logistical_Flow, Shop_Ens))

I know that I didn't use all the dimension from the cyclic groups because I don't get the wright result.

This is an example that I use to make things a little bit clearer:

     

This is what the result should be, the Count is correct for SD_Dest_Pallet but the Sum for SD_Shipped_Colli is not.

Month;SD_Delivery_Date;Aisle_Facturation;Shop_Ens;SD_Dest_Pallet;SD_Shipped_Colli;SD_Dest_Pallet

jun;20150618;PBL_OVERIGE;Price_List Partner;554001020020431209;31;1

jun;20150618;PBS_OVERIGE;Price_List Partner;554001020020000047;3;1

This example is also visible in the attached qvw.

I would really happy if someone could help me figure this out. there is still something missing but I don't see what.

Kind regards,

Monique