Calculation before aggregation

[0tkali]

I wanted to calculate new customers added every month based on the data below

I want below output

Calculation I tried

if(min(total [Order],[Customer],[Month.autoCalendar.YearMonth] [Month])=min(total <[Customer]>[Month]),1,0)

I get 0 for all the rows when I have only month in a straight table along with the calculation. I am able to see correct calculated values per customer when I add customer dimension to the table but once I remove the customer dimension is the values are all wrong again. Can someone please help.

[Shubham_Deshmukh]

I think I am getting the correct result, can you please check your data?

[sunny_talwar]

Yes, it does... it will check if the Customer is equal to the Customer on the row right above it or not... this is why having Customer as a first dimension is important as we need to first sort by Customer and then ascending order of Month for this to work

[0tkali]

---

@Shubham_Deshmukh wrote:

I think I am getting the correct result, can you please check your data?

---

=Count(DISTINCT 
{
<[Customer Name] = 
{
"=Count
(
{<
[Order Date]= 
{'<=$(=Date(Max([Order Date])))'}
>}

[Customer Name] 
) =1"

}

>

} [Customer Name])

Can you please explain how the above works? I will try with my data and update.

[0tkali]

---

@sunny_talwar wrote:

Yes, it does... it will check if the Customer is equal to the Customer on the row right above it or not... this is why having Customer as a first dimension is important as we need to first sort by Customer and then ascending order of Month for this to work

---

So within Aggr , does Aggr always sort the StructuredParameters we specify? How do you sort by desc? Also, there are chances that Customer/Order combination has duplicates. So do we eliminate duplicates?

Edit: Got to know that  Aggr supported ordering. How do I eliminate duplicates for Customer/Order combination.

[sunny_talwar]

---

@0tkali wrote:
How do I eliminate duplicates for Customer/Order combination.

---

What exactly do you mean by this? Can you show this by a sample and the output you expect to see when this happens?

[Shubham_Deshmukh]

Hi,
It will give you count of only those which customers are coming one time only.

[0tkali]

---

@sunny_talwar wrote:

---

@0tkali wrote:
How do I eliminate duplicates for Customer/Order combination.

---

What exactly do you mean by this? Can you show this by a sample and the output you expect to see when this happens?

---

Apologies, your expression handles this scenario, even if there are duplicates, sorting takes care of this and the count is perfect. Appreciate your help.

[0tkali]

---

@Shubham_Deshmukh wrote:
Hi,
It will give you count of only those which customers are coming one time only.

---

I still don't understand, is it possible to elaborate? I am attaching the dataset used(Orders sheet).

=Count(DISTINCT 
{
<[Customer Name] = 
{
"=Count // what does Count({}) = 1 do?
(
{< // what does this do?
[Order Date]= 
{'<=$(=Date(Max([Order Date])))'} //this gives max date in dataset which is Dec 31 2018
>}

[Customer Name] 
) =1"

}

>

} [Customer Name]) 

[sunny_talwar]

Sounds good. Happy to help 🙂

[Shubham_Deshmukh]

Brother,

Count means it will check for count =1 and max date is showing max till that month only as our dimension is month(date).

Dimension works like group by clause, so it restricts it upto max date and for count = 1 customers.