topic Count customers in App Development

Count customers

spividori — Wed, 22 Nov 2017 12:03:52 GMT

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

shraddha_g — Wed, 22 Nov 2017 12:05:41 GMT

Please provide sample data and Sample app with expected output

Re: Count customers

Ralf_Heukäufer — Wed, 22 Nov 2017 12:16:11 GMT

Try

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

perhabs you need additional an aggregation (aggr)

but to judge that I need more information.

Re: Count customers

sunny_talwar — Wed, 22 Nov 2017 12:19:28 GMT

May be you need this

LOAD ID_Cliente&period as Key

and then this

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

Re: Count customers

spividori — Wed, 22 Nov 2017 13:26:36 GMT

Thanks for answering.

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

sunny_talwar — Wed, 22 Nov 2017 13:32:27 GMT

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

Re: Count customers

spividori — Wed, 22 Nov 2017 23:24:34 GMT

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

spividori — Thu, 23 Nov 2017 15:12:33 GMT

Hi.

Any help?.

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

Regards.

Re: Count customers

Ralf_Heukäufer — Thu, 23 Nov 2017 18:59:43 GMT

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

if (sum(Cantidad)>0, count(distinct ID_Cliente))

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

Re: Count customers

spividori — Thu, 23 Nov 2017 19:59:17 GMT

Hi.

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

Regards.

Re: Count customers

sunny_talwar — Thu, 23 Nov 2017 22:49:12 GMT

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)

Re: Count customers

spividori — Fri, 24 Nov 2017 12:26:49 GMT

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.

Re: Count customers

sunny_talwar — Fri, 24 Nov 2017 12:53:02 GMT

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