12 Replies Latest reply: Nov 24, 2017 7:53 AM by Sunny Talwar

# Count customers

He.

In my example I need to count the customers where the sum of their sales is greater than 0 and I need to visualize it by different dimensions: time, seller, region, product, etc.

In the column Cant1 I use the formula:if(Sum(Precio * Cantidad * Cotizacion) > 0,count(distinct ID_Cliente)) but 1582 is not the correct total.

I also tried with:Count({\$<ID_Cliente= {"=sum(Precio * Cantidad * Cotizacion) > 0"}>} distinct ID_Cliente) but it does not work, when selecting a period it gives a result and when seeing all the periods the result in that period changes.

Other: Count({\$<ID_Ticket = {"=sum(Precio * Cantidad * Cotizacion) > 0"}>} distinct ID_Cliente) but takes into account for each ticket and if the sum, for example, of a period is not greater than zero, it counts and should not do so.

Regards.

• ###### Re: Count customers

Please provide sample data and Sample app with expected output

• ###### Re: Count customers

Try

count(if(Sum(Precio * Cantidad * Cotizacion) > 0,distinct ID_Cliente))

perhabs you need additional an aggregation (aggr)

• ###### Re: Count customers

May be you need this

and then this

Count({\$<Key = {"=Sum(Precio * Cantidad * Cotizacion) > 0"}>} DISTINCT ID_Cliente)

• ###### Re: Count customers

But if ID_Cliente&period 1 = \$ 100 and ID_Cliente&period 2 = \$ -100 you should not count and count it.

I'm going to try to put together a set of data and upload the qvf.

Regards.

• ###### Re: Count customers

Ya, a sample might help us understand your issue better here

• ###### Re: Count customers

Hi.

Here is the example qvf.

In Table1 it shows the correct result, but if I remove the filter for the month of January, it shows another result for January.

In Table2, dimension Rubro_id, it does not work.

In Table3 the column "Cant" works correctly, but the Total is incorrect.

I would like to show the result alternating several dimensions, not just time.

Regards.

• ###### Re: Count customers

date(FechaComprobante) as Date,

month(FechaComprobante) as Month,

year(FechaComprobante) as Year,

Clave as ID_Cliente,

SitioClienteVta as PuntoVta,

Rubro_id,

idVendedor,

FROM [lib://Forum/Ventas2017.xlsx]

(ooxml, embedded labels, table is Sheet1);

and then use this

Count({\$<Key = {"=sum(Cantidad) > 0"}>} DISTINCT ID_Cliente)

• ###### Re: Count customers

Hi Sunny.

Your example works perfect to see it by year, month, but, to work in the dimension

"Rubro_id" should be added to the Key:

AutoNumber(Clave&month(FechaComprobante)&year(FechaComprobante)&Rubro_id) as Key.

And so do with the other dimensions.

Regards.

• ###### Re: Count customers

Sounds good... I was not sure what level you wanted to check this at and hence you might have to modify it based on your requirement.... Alternatively, you can use Aggr() function on the front end to do the same....

• ###### Re: Count customers

Hi.

Any help?.

The only way to solve it is using the aggr function?.

Regards.

• ###### Re: Count customers

Are you shure that 973 is correct for january if you use

you have allways the same solution in your calculation but its 988 for january.

• ###### Re: Count customers

Hi.

For January corresponds 973. By selecting January and exporting Table3 to excel, you can count them to 973 clients.

Regards.