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

[asianasian]

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.

Please help me for this issue.

[Ezirraffner]

Hi @asianasian,

Would it just be a sum? See...

Sum([No of customer])

Regards

Ezir 

[asianasian]

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]

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

[asianasian]

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]

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 · Accepted solution]

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]))