Qlik Community

Ask a Question

App Development

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

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
asianasian
Contributor II
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. 

 

groupcustomersales datesalesNo of customer
1a2019/07/15-799000-1
1b2019/07/15-286400-1
1c2019/07/15860001
1d2019/07/152310001
1e2019/07/153010001
1f2019/07/153262281
1g2019/07/154620001
1h2019/07/1513164001

 

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 dategroupvisitingno of customersales
2019/07/15186¥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.

Please help me for this issue.

Labels (8)
1 Solution

Accepted Solutions
asianasian
Contributor II
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])) 

View solution in original post

9 Replies
Ezir
Creator II
Creator II

Hi @asianasian,

Would it just be a sum? See...

Sum([No of customer])

 

Regards

 

Ezir 

asianasian
Contributor II
Contributor II
Author

thank you for your reply.

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

sunny_talwar

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)
asianasian
Contributor II
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! 

sunny_talwar

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

image.png

asianasian
Contributor II
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.

 

asianasian
Contributor II
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.

sunny_talwar

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

asianasian
Contributor II
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])) 

View solution in original post