6 Replies Latest reply on Aug 15, 2019 9:23 PM by Udit Chowdhry

# Distinct count of customer ids

Hi all,

I want to calculate these three things below and display only in one view

Sum of distinct customer id for a given date

Sum of distinct customer id which have an issued today

Sum of distinct customer id which have an issued in past

You can use the superstore dataset for this query.

So how it is possible if anybody knows these type of calculation so please respond to my query asap.

Thanks,

Udit

• ###### 1. Re: Distinct count of customer ids

Hi,

Try this,

create a calculated filed as shown

For the first questionc

distinct customer id:

{ FIXED [Order Date]:COUNTD([Customer ID])}

Use this filed and filter for a date to show the Sum of distinct customer id

For the next two questions, you can use a calculated field to filter the values  as shown,

Use the distinct customer id: calculation and filter the date using the below instead of choosing a filler date

distinct customer id which have an issued today:

[Order Date]=TODAY()

OR

[Order Date]={MAX([Order Date])}  (FOR recent date)

Drag this to filter and select TRUE

For the past date you can modify the above calculation as shown

[Order Date]<TODAY()

OR

[Order Date]<{MAX([Order Date])}  (FOR recent date)

You can also make use of parameter to show all of them as per selection

Hope this helps!

If this reply helps, mark it as helpful/correct

Manoj

• ###### 2. Re: Distinct count of customer ids

Hi, Udit

try this

1. Countd(if [your date field] = Date("2019-08-08") then [Customer ID] end)

2. Countd(if [your date field] = Today() then [Customer ID] end)

3. Countd(if [your date field] < Today() then [Customer ID] end)

Let me know if you have question.

ZZ

1 of 1 people found this helpful
• ###### 3. Re: Distinct count of customer ids

If you want to pick that given date yourself by a filter then change the first calculation of Zhouyi Zhang to

Countd(if [your date field] = [your date field] then [Customer ID] end)

and use a filter on it. You need to put this one on a seperated worksheet because the datefilter can't be applied to the 2 other calculations.

• ###### 4. Re: Distinct count of customer ids

Hi,

Thanks for your answer

• Total Customers – Sum of distinct customer id for a given date
• New Customers – Sum of distinct customer id which have an issued today
• Repeat Customers - Sum of distinct customer id which have an issued in past

If I want to calculate these three things then same calculation will be used ???

Thanks,

Udit

• ###### 5. Re: Distinct count of customer ids

Hi, Udit

It will depend on how your data looks like, my sample calculation is working for normal case, so if you can provide a sample workbook, it will be easier to say yes/no.

ZZ

• ###### 6. Re: Distinct count of customer ids

Hi,

I am not able to share the datatset. You can use Sample Superstore Dataset

And one more thing how to represent these three cases in one view Which chart is best suitable for this scenario

Thanks,

Udit