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]

Awesome

I am glad you got what you wanted.

Best,

Sunny