topic While Applying Aggregation Expression level in chart its changing the output Result in QlikView

While Applying Aggregation Expression level in chart its changing the output Result

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

Hi All Qlik Experts,

I am facing one issue in Aggr function.

Scenario:

I need the count of customer who has been missed to be server particular product Rolling 4 Weeks.

so i applied aggr functiion, at summary level the count of customer is not coming proper but if i select any one product its coming proper.

Expression is :

=count({<[Distribution R12W]={9,10,11,12},Flag1={'Distribution'}, [All Targeted SKU Flag]={1}>} aggr(if(max({<[Distribution R12W]={9,10,11,12},[All Targeted SKU Flag]={1}>}[Served SKU Flag])=0, Customer),Customer))

For more info please refer the attached screenshot.

Thanks,

Dhruv.

Re: While Applying Aggregation Expression level in chart its changing the output Result

andrey_krylov — Mon, 27 Aug 2018 03:47:33 GMT

Hi, Dhruv. Try this

=Sum({<[Distribution R12W]={9,10,11,12},Flag1={'Distribution'}, [All Targeted SKU Flag]={1}>} if(aggr(max({<[Distribution R12W]={9,10,11,12}, TOTAL<Customer> [All Targeted SKU Flag]={1}>}[Served SKU Flag])=0, Customer), 1))

Re: While Applying Aggregation Expression level in chart its changing the output Result

dhruvkumar_josh — Mon, 27 Aug 2018 05:53:10 GMT

Thanks Andrey for your quick reply.

But when i am using your suggested expression is not working becuase of that Total <Customer>. can you please recheck and help it.

=Sum({<[Distribution R12W]={9,10,11,12},Flag1={'Distribution'}, [All Targeted SKU Flag]={1}>} if(aggr(max({<[Distribution R12W]={9,10,11,12}, TOTAL<Customer> [All Targeted SKU Flag]={1}>}[Served SKU Flag])=0, Customer), 1))

Thanks,

Dhruv

Re: While Applying Aggregation Expression level in chart its changing the output Result

Anil_Babu_Samineni — Mon, 27 Aug 2018 07:00:34 GMT

I think this will suffice

Count(aggr(if(max({<[Distribution R12W]={9,10,11,12},[All Targeted SKU Flag]={1}>}[Served SKU Flag])=0, Customer),Customer))

Re: While Applying Aggregation Expression level in chart its changing the output Result

dhruvkumar_josh — Mon, 27 Aug 2018 08:28:09 GMT

Thanks Anil,

While applying your given expression , it's only giving me 1 Product code and doesn't shows other code .

can you please check it because the logic wise my expression is correct but not working properly.

Thanks.

Re: While Applying Aggregation Expression level in chart its changing the output Result

Anil_Babu_Samineni — Mon, 27 Aug 2018 13:50:21 GMT

Can you attach sample

Re: While Applying Aggregation Expression level in chart its changing the output Result

dhruvkumar_josh — Mon, 27 Aug 2018 14:01:19 GMT

Do you want image or Design part?

if the design part, then it is too heavy so not possible to attach. i

Re: While Applying Aggregation Expression level in chart its changing the output Result

BalaBhaskar_Qlik — Mon, 27 Aug 2018 16:58:40 GMT

May be this expression:

=if(max({<[Distribution R12W]={9,10,11,12},[All Targeted SKU Flag]={1}>}[Served SKU Flag])=0,

Aggr(

count({<[Distribution R12W]={9,10,11,12},Flag1={'Distribution'}, [All Targeted SKU Flag]={1}>}Customer),

Customer)

)

Re: While Applying Aggregation Expression level in chart its changing the output Result

BalaBhaskar_Qlik — Mon, 27 Aug 2018 16:59:21 GMT

May be try this:

=Count(if(max({<[Distribution R12W]={9,10,11,12},[All Targeted SKU Flag]={1}>}[Served SKU Flag])=0,

Aggr(

count({<[Distribution R12W]={9,10,11,12},Flag1={'Distribution'}, [All Targeted SKU Flag]={1}>}Customer),

Customer)

))

Re: While Applying Aggregation Expression level in chart its changing the output Result

sunny_talwar — Mon, 27 Aug 2018 17:15:41 GMT

What exactly is your dimension in the chart above? Is it Customer of something else?

Re: While Applying Aggregation Expression level in chart its changing the output Result

andrey_krylov — Mon, 27 Aug 2018 18:34:55 GMT

Sorry Dhruv. I see my error and now it should work

=Sum({< [Distribution R12W] = {9,10,11,12}, Flag1 = {'Distribution'}, [All Targeted SKU Flag] = {1} >}

aggr(

if(

max({< [Distribution R12W] = {9,10,11,12}, [All Targeted SKU Flag] = {1}>} TOTAL<Customer> [Served SKU Flag]) = 0

, 1)

, Customer)

)

Re: While Applying Aggregation Expression level in chart its changing the output Result

dhruvkumar_josh — Tue, 28 Aug 2018 12:32:20 GMT

Hi Sunny,

The Dimension which i used in chart is Product but i want product wise Customer count.

Thanks,
Dhruv

Re: While Applying Aggregation Expression level in chart its changing the output Result

dhruvkumar_josh — Tue, 28 Aug 2018 12:35:51 GMT

Thanks Andrew,

I used given expression, it's also changing the values . i tried all the scenario here. does not working

Re: While Applying Aggregation Expression level in chart its changing the output Result

sunny_talwar — Tue, 28 Aug 2018 12:38:07 GMT

So, may be you need this

=Count({<[Distribution R12W] = {9,10,11,12}, Flag1 = {'Distribution'}, [All Targeted SKU Flag] = {1}>} Aggr(If(Max({<[Distribution R12W] = {9,10,11,12}, [All Targeted SKU Flag] = {1}>} [Served SKU Flag]) = 0, Customer), Customer, Product))

Re: While Applying Aggregation Expression level in chart its changing the output Result

dhruvkumar_josh — Tue, 28 Aug 2018 13:25:08 GMT

Thanks Sunny,

I will try to use this function and will update.

Re: While Applying Aggregation Expression level in chart its changing the output Result

dhruvkumar_josh — Thu, 30 Aug 2018 09:57:57 GMT

Dear Sunny,

I applied your Expression , while i select the product its working fine but when i deselect the product my chart showing Error or sometimes its stuck and need to forcefully close the design application.

Re: While Applying Aggregation Expression level in chart its changing the output Result

sunny_talwar — Thu, 30 Aug 2018 11:07:21 GMT

It is difficult to say, would you be able to share a sample?

Preparing examples for Upload - Reduction and Data Scrambling