Sum Aggr Count

[#1]

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.

[sunny_talwar]

Try this:

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

[#3]

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

[sunny_talwar]

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

Best,

Sunny

[#5]

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

[#6]

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.

[sunny_talwar]

Here you go: (PFA)

Hope this is what you are looking to get.

Best,

Sunny

[#8]

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

I didn't understand your answer.

Thanks in advice.

[sunny_talwar · Accepted solution]

Yes I did add an attachment.

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

Best,

Sunny

[#10]

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

Thanks a lot sunindia‌ .