Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Counting distinct subsequent buyer

hallo community,

i have a big problem.

I want to count distinct subsequent buyer 

but in my data i have also returns of my buyers.

So i only want to count Customers who really bought more than once

The Order-Value of a return is negativ.

 

Right now im using this:

 

count(
distinct

{<
[Customer-ID]={"=count(distinct [ORDER-ID])>1"},
[ORDER-VALUE]={"=[ORDER-VALUE]>0"}
>}

[Customer-ID])

 

I think the term "[ORDER-VALUE]={"=[ORDER-VALUE]>0"}" is wrong.

For the subsequent buyer I only want to consider distinct Order ID,

more than one Order and for all distinct Order-IDs their Order-Value is bigger than 0.

 

Thank you for your help

 

 

 

Labels (1)
4 Replies
Highlighted
MVP
MVP

@canerhan  try below. If does not work can you share sample data with expected output?

count(
distinct

{<
[Customer-ID]={"=count(distinct {<[ORDER-VALUE]={'>0'}>}[ORDER-ID])>1"},
[ORDER-VALUE]={">0"}
>}

[Customer-ID])
Highlighted
Contributor
Contributor

@Kush  Thanks,

 

i get 0 as a result. The Problem is the term "  {<[ORDER-VALUE]={'>0'}   ".

Can this be enough? :

{<
[Customer-ID]={"=count(distinct  [ORDER-ID])>1"},
[ORDER-VALUE]={">0"}
>}

[Customer-ID])

According to the Qlik Sense logic:

Shouldn´t the term [ORDER-VALUE]={">0"} also be considered in " [Customer-ID]={"=count(distinct  [ORDER-ID])>1"} "automatically in the endresult of the measure?

 

thank you for help

Highlighted
MVP
MVP

@canerhan  I don't see any error with expression, [ORDER-VALUE]={">0"}  is considered  but as you are using other set with expression it will override the condition and that is where same condition need to be added in inner set expression well which I already suggested.

if you can share sample data with expected output I can help you with accurate expression. Mean while you can also try below

count(
distinct

{<
[Customer-ID]={"=count(distinct [ORDER-ID])>1 and sum([ORDER-VALUE])>0"},
>}

[Customer-ID])
Highlighted

Not sure, but you can try this also

Count(DISTINCT {<[Customer-ID] = {"=Count(DISTINCT {<[ORDER-ID] = {[=Sum([ORDER-VALUE]) > 0]}>} [ORDER-ID]) > 1"}>} [Customer-ID])