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

[kamielrajaram]

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

[amber2000]

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

[kamielrajaram]

Hi,

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

Regards

Kamiel

[amber2000]

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

[kamielrajaram]

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

[amber2000]

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

[kamielrajaram]

Hi Monique,

Is the attached what you are looking for

[amber2000]

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

[kamielrajaram]

Hi Monique,

Attached Please find the qvw file.

Kind Regards

Kamiel

[amber2000]

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