How to count repeating values from 3 different columns

[#1]

Hi Guys,

Few months ago I posted a question and I got the write answer.

How to tag Orders with YES/NO if customer name is same?

This question relates to the above mentioned post.

What would be the right expression to count to total orders where following fields are same?

[OrderDate], [Name], [StoreName]

I tried following but I am getting incorrect totals

Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1

[Gysbert_Wassenaar]

Subtract it from the total count.

[sunny_talwar]

No problem at all

[#4]

Just one more addition in this same question.

Where can I add Set analysis in this expression if I only want those order where [OrderStatus] = 'Open'

[sunny_talwar]

May be like these:

=Count({<OrderStatus = {'Open'}>} DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> {<OrderStatus = {'Open'}>} OrderId) > 1, OrderId), OrderId, Name, OrderDate))

&

=Count({<OrderStatus = {'Open'}>} DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> {<OrderStatus = {'Open'}>} OrderId) = 1, OrderId), OrderId, Name, OrderDate))

[#6]

Thanks a lot.

[#7]

Hi Sunny,

You expression works perfect for counting repeating Customer Name, Order Date and Store Name.. but where can I add if statement to show 'YES' or 'NO' if those 3 values are repeating or not?

I am currently using following formula

If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1,'YES','NO')

But this formula is not showing right result for every order.

Below is your expression to count.. where can I tweak this to get 'YES' or 'NO' ?

=Count(DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1, OrderId), OrderId, Name, OrderDate))

[sunny_talwar]

Where exactly are you using this expression? In the straight table?

If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1,'YES','NO')

Seems to be working here, what is the issue?

[#9]

Hi Sunny, The issue is that I want to count how many of these dual orders have been cancelled? so

So Order ID 1 and 2 are dual orders but if one of them have status = 'Cancelled' then I want to know total how many Orders are cancelled which falls into dual order definition?

Count of both dual order cancelled

count of one of the dual order cancelled

[sunny_talwar]

In a text box object? May be this

Count of both dual order cancelled

=Count(DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1 and SubStringCount(Concat(DISTINCT Status, ','), 'Cancelled') = 1 and Count(DISTINCT Status) = 1, OrderId), OrderId, Name, OrderDate))

count of one of the dual order cancelled

=Count(DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1 and SubStringCount(Concat(DISTINCT Status, ','), 'Cancelled') = 1 and Count(DISTINCT Status) = 2, OrderId), OrderId, Name, OrderDate))