Count customers

[spividori]

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.

[sunny_talwar · Accepted solution]

Modify your script to this

LOAD AutoNumber(Clave&month(FechaComprobante)&year(FechaComprobante)) as Key,

    date(FechaComprobante) as Date,

    month(FechaComprobante) as Month,

    year(FechaComprobante) as Year,

    Clave as ID_Cliente,

    idFacturaVtad as Id_tickets,

    SitioClienteVta as PuntoVta,

    Rubro_id,

    idVendedor,

    Cantidad

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

(ooxml, embedded labels, table is Sheet1);

and then use this

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

[spividori]

Hi Sunny.

Thanks for answering.

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.

[sunny_talwar]

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....