9 Replies Latest reply on Feb 16, 2017 12:10 AM by sneha simha

# I have two fields sales and Quantity.

Dear All

I created a calculated field named  UCP and the formula being (sum(Sales)/sum(Quantity)).

But when i try to use this formula

if [Docdate]>=DATE(STR(if MONTH(TODAY()-1)>3

then YEAR(TODAY()-1) else YEAR(TODAY()-1)-1 end)+"-04-01")

and

[Docdate]<=TODAY()-1 then [UCP] end

It gives error saying cannot mix aggregate and non aggregate.

Kindly Help

Regards

Sneha

• ###### 1. Re: I have two fields sales and Quantity.

Hi Sneha,

your UCP calculation is aggregated one. so that error coming as "cannot mix aggregate and non aggregate".

Remove the sum in UCP else add attr in dimension.

Thanks

sankar

• ###### 2. Re: I have two fields sales and Quantity.

Hi Sneha,

You can try something like this,

Create two calculated fields

UCP Sales:

if [Docdate]>=DATE(STR(if MONTH(TODAY()-1)>3

then YEAR(TODAY()-1) else YEAR(TODAY()-1)-1 end)+"-04-01")

and

[Docdate]<=TODAY()-1 then [Sales] end

UCP Quantity:

if [Docdate]>=DATE(STR(if MONTH(TODAY()-1)>3

then YEAR(TODAY()-1) else YEAR(TODAY()-1)-1 end)+"-04-01")

and

[Docdate]<=TODAY()-1 then [Quantity] end

Then calculate UCP:

SUM([UCP Sales])/SUM([UCP Quantity])

Let me know If this help.

Mahfooj

2 of 2 people found this helpful
• ###### 3. Re: I have two fields sales and Quantity.

Hi Sankar

I cannot share my workbook here sorry but when i uses attr instead of sum it shows no values at all.

Regards

Sneha

• ###### 4. Re: I have two fields sales and Quantity.

Hi Mafooj,

Hey Thank you so much , was looking for something like this to calculate the growth.It Worked Great Thanks again.

Regards

Sneha

• ###### 5. Re: I have two fields sales and Quantity.

You are welcome sneha

Mahfooj

• ###### 6. Re: I have two fields sales and Quantity.

Hi Mahfooj

Regards

Sneha Simha

• ###### 8. Re: I have two fields sales and Quantity.

Thank you

Regards

Sneha Simha

• ###### 9. Re: I have two fields sales and Quantity.

Hi Mahfooj