Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
spividori
Specialist
Specialist

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.

Captura1.PNG

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.

1 Solution

Accepted Solutions
sunny_talwar

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)


Capture.PNG

View solution in original post

12 Replies
shraddha_g
Partner
Partner

Please provide sample data and Sample app with expected output

Ralf_Heukäufer
Partner
Partner

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.

sunny_talwar

May be you need this

LOAD ID_Cliente&period as Key

and then this

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

spividori
Specialist
Specialist
Author

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.

sunny_talwar

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

spividori
Specialist
Specialist
Author

Hi.

Here is the example qvf.

Captura1.PNG

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.

spividori
Specialist
Specialist
Author

Hi.

Any help?.

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

Regards.

Ralf_Heukäufer
Partner
Partner

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.

spividori
Specialist
Specialist
Author

Hi.

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

Regards.