# App Development

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for
Did you mean:
Contributor II

## *URGENT* count the number of customer whose "daily" sales greater than 0

GREETINGS EXPERTS,

I want to ask a simple to you experts, but so completed issue for me to solve.

I want to count the number of customers whose daily sales are greater than 0.

below is the raw data of my analysis.

 group customer sales date sales No of customer 1 a 2019/07/15 -799000 -1 1 b 2019/07/15 -286400 -1 1 c 2019/07/15 86000 1 1 d 2019/07/15 231000 1 1 e 2019/07/15 301000 1 1 f 2019/07/15 326228 1 1 g 2019/07/15 462000 1 1 h 2019/07/15 1316400 1

for 2 customers, the sum of the sales is less than 0, so they should be counted to be negative number,

and for 6 customers, the sum of the sales is greater than 0, so they are counted to be positive number.

(※ I don't know how many bills they made or how many products thy bought, I would just ignore this info)

I want to show the whole customer for group 1 of the date of '19/07/15' to be calculated as 4 (-2+6)

I looked up on this forum to find a function, and applied to mine, but it didn't exactly fit.

this is the function that i used.

Count(Distinct {<Sales = {"=Sum(Sales)>0"}> } Customer) - Count(Distinct {<Sales = {"=Sum(Sales)<0"}> } Customer)

or

Count({<[sales]= {"=Sum(distinct [sales])>0"}>} distinct [customer]) - Count({<[sales]= {"=Sum(distinct [sales])<0"}>} distinct [customer])

I tried both, and the results were same.  Below is the result i got with these functions

 sale date group visiting no of customer sales 2019/07/15 1 8 6 ¥1,637,228

number of customer should be 4 as i stated above, but it shows 6 for some reasons.

I assume it is because i didnt add date condition in the function. but i don't know how to handle it.

Labels (8)

• ### sumif

1 Solution

Accepted Solutions
Contributor II
Author

Thanks for all the helps.

I solved this problem using code below. please see if someone else has similar issues.

count( DISTINCT if ( aggr(sum([sales]) , [customer ID], [date]) > 0, [customer ID])) -

count( DISTINCT if ( aggr(sum([sales]) , [customer ID], [date]) < 0, [customer ID]))

9 Replies
Creator II

Hi @asianasian,

Would it just be a sum? See...

``Sum([No of customer])``

Regards

Ezir

Contributor II
Author

im sorry that i didnt make it clear, i have sales data for each group, and i need to calculate using it in excel file.

so im trying to figure out how to count no of costomer

MVP

Do you select a single date when you look at the result? If you do, try this

``````Count(Distinct {<Customer = {"=Sum(Sales) > 0"}>} Customer) -
Count(Distinct {<Customer = {"=Sum(Sales) < 0"}>} Customer)``````

If you do not, then create a new field in the script like this

``````LOAD Customer,
[sales date],
Customer & '|' & [sales date] as CustomerSalesDateKey,
...
FROM ...;``````

and then try something like this

``````Count(Distinct {<CustomerSaleDateKey = {"=Sum(Sales) > 0"}>} Customer) -
Count(Distinct {<CustomerSaleDateKey = {"=Sum(Sales) < 0"}>} Customer)``````
Contributor II
Author

thank you for your reply. im using single data and tried the first one. and i showed the same result as i tried with the code i stated in the body.

i will try your second suggestion!

MVP

Not sure why we get different results, but with the sample data shared... I see this

Contributor II
Author

thank you so much for your situation.

Actually i downloaded the most specific raw data using other query sheet.

i chose some variables from the server, and i dont exactly know the basic unit of count.

i chose date, group (of customer), passport no, sales and some other variables. i thought the passport no would be the basic unit of calculation and tried that code, and it didnt work properly. i would try again.

Contributor II
Author

im afraid that i missed that ID No is combined with date of sales, so when i write code like "count ID No when salea is greater than 0", qliksense may proceed without considering single dates.

So, maybe it calculated each persons whole sales data in certain period, not for a single date.

MVP

I am not sure I understand... is all good now?

Contributor II
Author

Thanks for all the helps.

I solved this problem using code below. please see if someone else has similar issues.

count( DISTINCT if ( aggr(sum([sales]) , [customer ID], [date]) > 0, [customer ID])) -

count( DISTINCT if ( aggr(sum([sales]) , [customer ID], [date]) < 0, [customer ID]))

Tags
Community Browser