topic Sum Aggr Count in QlikView

Sum Aggr Count

Wed, 13 May 2015 01:33:54 GMT

Hello guys.

I have the following set analysis in my model to sum the count of distinct bills of the previous year:

=Sum(Aggr(Count({$<YEAR =, MONTH_NUMBER_OF_YEAR =, DATE={">=$(vMinDateComparable)<=$(vMaxDateComparable)"}>} DISTINCT KXBILL), DXCUSTOMERDETERMINANT))

With this set analysis, I'm getting the following results:

The problem is that there's something wrong with it, I'm not getting the correct results, the correct results are the following with this set analysis:

=Count({$<YEAR =, MONTH_NUMBER_OF_YEAR =, DATE={">=$(vMinDateComparable)<=$(vMaxDateComparable)"}>} DISTINCT KXBILL)

You can see that for some reason, with the aggr function its excluding some stores (for example: "NG Acoxpa").

I cannot use the last formula because in the grand total its also counting the distinct values, it's not doing a sum as I expect. The result of this should be: 37084, not 11258 nor 33591 as shown in the first formula.

As a tip: in a cube with Microsoft SSAS Tabular Mode, I'm solving this using the following formula:

TRANSACCIONES AÑO ANTERIOR:=CALCULATE(SUMX(SUMMARIZE(VENTAS, VENTAS[DXCUSTOMERDETERMINANT], VENTAS[KNDATE], "TRANSACCIONES", DISTINCTCOUNT(VENTAS[KXBILL])), [TRANSACCIONES]), (DATEADD(FECHAS[FECHA],-364,DAY)))

Note that I'm grouping using two columns, dxcustomerdeterminant and kndate, but if I apply the KNDATE in the qlikview formula the formula will look like this:

=Sum(Aggr(Count({$<YEAR =, MONTH_NUMBER_OF_YEAR =, DATE={">=$(vMinDateComparable)<=$(vMaxDateComparable)"}>} DISTINCT KXBILL), DXCUSTOMERDETERMINANT, KNDATE))

And will display cero as result:

If anyone could help, I would appreciate it a lot.

Thanks in advice.

Re: Sum Aggr Count

sunny_talwar — Wed, 13 May 2015 15:49:22 GMT

Try this:

=Sum(Aggr(NODISTINCT Count({$<YEAR =, MONTH_NUMBER_OF_YEAR =, DATE={">=$(vMinDateComparable)<=$(vMaxDateComparable)"}>}DISTINCT KXBILL), DXCUSTOMERDETERMINANT))

Re: Sum Aggr Count

Wed, 13 May 2015 16:10:20 GMT

It didn't work, it's showing the same result "Transacciones AA":

Re: Sum Aggr Count

sunny_talwar — Wed, 13 May 2015 16:14:31 GMT

Would you be able to share your application to check it?

Best,

Sunny

Re: Sum Aggr Count

Wed, 13 May 2015 16:57:17 GMT

no papi eso esta bien fácil, si no puedes me avisas!!

Re: Sum Aggr Count

Wed, 13 May 2015 19:51:42 GMT

There you go.

Here I leave the link of the application:

Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.

Thanks in advise.

Re: Sum Aggr Count

sunny_talwar — Thu, 14 May 2015 10:27:30 GMT

Here you go: (PFA)

Hope this is what you are looking to get.

Best,

Sunny

Re: Sum Aggr Count

Thu, 14 May 2015 13:52:08 GMT

I'm sorry I didn't get it, Did you attach something?,

I didn't understand your answer.

Thanks in advice.

Re: Sum Aggr Count

sunny_talwar — Thu, 14 May 2015 13:56:26 GMT

Yes I did add an attachment.

Open the link: Re: Sum Aggr Count‌ to see the attachement

Best,

Sunny

Re: Sum Aggr Count

Thu, 14 May 2015 14:05:09 GMT

Yes, that's exactly what I was looking for.

Thanks a lot sunindia‌ .

Re: Sum Aggr Count

sunny_talwar — Thu, 14 May 2015 14:06:00 GMT

Awesome

I am glad you got what you wanted.

Best,

Sunny