topic Re: Count Aggr on Dimension anomaly in QlikView

Count Aggr on Dimension anomaly

amber2000 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Count Aggr on Dimension anomaly

sunny_talwar — Thu, 12 May 2016 11:13:25 GMT

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

Re: Count Aggr on Dimension anomaly

swuehl — Thu, 12 May 2016 11:20:34 GMT

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.

Re: Count Aggr on Dimension anomaly

kamielrajaram — Thu, 12 May 2016 11:26:34 GMT

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

Re: Count Aggr on Dimension anomaly

amber2000 — Tue, 17 May 2016 16:53:24 GMT

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

Re: Count Aggr on Dimension anomaly

swuehl — Tue, 17 May 2016 17:01:46 GMT

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.

Re: Count Aggr on Dimension anomaly

kamielrajaram — Wed, 18 May 2016 07:41:42 GMT

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

Re: Count Aggr on Dimension anomaly

amber2000 — Wed, 18 May 2016 10:37:41 GMT

Hi Kamiel

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

Monique

Re: Count Aggr on Dimension anomaly

amber2000 — Wed, 18 May 2016 11:01:27 GMT

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

Re: Count Aggr on Dimension anomaly

amber2000 — Tue, 24 May 2016 16:29:03 GMT

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

Re: Count Aggr on Dimension anomaly

kamielrajaram — Wed, 25 May 2016 07:14:20 GMT

Hi Amber,

I do apologize, as I was a bit busy. Would it be possible to get a reduced model.

Can you please try this

Sum(

aggr(

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

Year, Month, WeekDay, Aisle_Facturation, SD_Delivery_Date, Logistical_Flow, Shop_Ens))

Re: Count Aggr on Dimension anomaly

amber2000 — Wed, 25 May 2016 09:24:43 GMT

Hi Kamiel,

No problem, I'm so sorry that I can't seem to fix this issue but I'm learning so much about Aggr() now from you guys.

I've tried your new statement but the result isn't correct.

Is the qvw i've attached yesterday to big to test?

I'll attach a smaller model also provide in excel how the results should look like.

Kind regards,

Monique

Re: Count Aggr on Dimension anomaly

kamielrajaram — Wed, 25 May 2016 10:23:48 GMT

Hi,

Thanks got it. Will have a look now and let you know

Regards

Kamiel

Re: Count Aggr on Dimension anomaly

amber2000 — Wed, 25 May 2016 10:46:33 GMT

I have tried a new expression so that from Aisle_Zone the 2 values are exlcuded.

Every Time I try the combination Aisle_Facturation, Logistical_Flow everything goes wrong. Lik you told me before I have to use all Dimensions in the aggr()

=Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }

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

If I send you a simpler model then you won't see what is going wrong.

I'll look further to simulate the strange behavior in a smaller model.

Thanks in advance for trying to help me, I'm getting really desperate.

Monique

Re: Count Aggr on Dimension anomaly

kamielrajaram — Wed, 25 May 2016 13:06:35 GMT

Hi Monique,

Please try this and let me know if it helps.

1. Set up 2 variables

Variable name - vCurrentAisle

Variable Value = GetCurrentField(Aisle) --this is your group

Variable name - vCurrentPeriods

Variable Value = GetCurrentField(Period) --this is your group

Expression:

Sum(aggr(Count({$ <[SD_WH] -= {'12'}, [Aisle_Zone] -= {'PBS_Containers, PBS_Displays'}> } SD_Dest_Pallet),$(vCurrentAisle),$(vCurrentPeriods)))

Regards

Kamiel

Re: Count Aggr on Dimension anomaly

amber2000 — Wed, 25 May 2016 15:14:47 GMT

Kamiel,

When I add the variable Value My group isn't recognized, only individual fields are allowed and that gives 0 as expression result.

Could it be that the Count(distinct SD_Dest_Pallet) value can't be aggregated in this case because of the different granularity of the fields Logistical_Flow and Aisle_Facturation.

One logistical_Flow can contain more Aisle_Facturation but Aisle_Facturation can only belong to 1 Logistical_Flow.

Kind regards,

Monique

Re: Count Aggr on Dimension anomaly

kamielrajaram — Wed, 25 May 2016 20:42:26 GMT

Hi Monique,

Is the attached what you are looking for

Re: Count Aggr on Dimension anomaly

amber2000 — Thu, 26 May 2016 15:07:21 GMT

Hi Kamiel,

I looks OK but I have to see the data in detail.

What did you do to get this result?

Kind regards,

Monique

Re: Count Aggr on Dimension anomaly

kamielrajaram — Fri, 27 May 2016 00:00:52 GMT

Hi Monique,

Attached Please find the qvw file.

Kind Regards

Kamiel

Re: Count Aggr on Dimension anomaly

amber2000 — Fri, 27 May 2016 14:08:19 GMT

Hi Kamiel,

Thank you for your patience.

In a few hours I'm leaving on holiday so I can't test your solution a this moment.

I wont forget to give you an answer when I get back so we can close this difficult issue.

kind regards,

Monique