7 Replies Latest reply on Apr 2, 2018 2:14 AM by Yuriy Fal

# Aggregate Calc in Fiscal YTD Price Per Unit

Hi All,

I'm trying to calculate Price Per Unit for all products sold to a Customer by Month (showing in fiscal year) when a user selects YTD (In the parameter). The formula is as follows.

IF

DATEDIFF('year', [Order Date], TODAY())=0

AND

DATE([Order Date])< TODAY()

THEN

[Price per Unit]

END

this errors out as expected "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions"

So, I tried this

IF

ATTR(DATEDIFF('year', [Order Date], TODAY()))=0

AND

ATTR(DATE([Order Date]))< TODAY()

THEN

[Price per Unit]

END

This calculation doesn't give errror, but, the results are not showing correct. What am I doing wrong here? Can someone please throw some light?

I'd really appreciate it.

Thanks,

KK

• ###### 1. Re: Aggregate Calc in Fiscal YTD Price Per Unit

pl check screenshot and calcs in attached

Thanks

Deepak

1 of 1 people found this helpful
• ###### 2. Re: Aggregate Calc in Fiscal YTD Price Per Unit

Change the calculation to

IF INDEX() <= MONTH(today()) THEN [Price per Unit] END

• ###### 3. Re: Aggregate Calc in Fiscal YTD Price Per Unit

Hi Deepak,

Thanks for the response and your time trying to help me solve this.

In your calc for YTD price per Unit below you hardcoded the month number. But I need it dynamic instead. Do yo have a tweak to this?

My requirement is to plot a trend chart comparing 'current Year' price per unit with prior year's. And hence the dynamic calc.

IF

ATTR((Month([Order Date])))<= 3

THEN

SUM([YTD Price per Unit (Error)])

END

I added a new cal in the workbook as

IF

ATTR(DATEDIFF('year', [Order Date], TODAY()))=0

AND

ATTR(DATE([Order Date]))< TODAY()

THEN

SUM([YTD Price per Unit (Error)])

END

when I try to use this, it's the same result, blank.

Thanks,

Kranthi

• ###### 4. Re: Aggregate Calc in Fiscal YTD Price Per Unit

Hi Kranthi,

Now look at Calc 4 and 5, Your ATTR has to cover all of your logic. You were leaving Today() out of ATTR.

If this helps, pl mark it Helpful and CORRECT to close Thread.

Thanks

Deepak

• ###### 5. Re: Aggregate Calc in Fiscal YTD Price Per Unit

Hi Kranthi,

You may want to place the YTD filter conditions

inside the corresponding (Row-Level) calculations

(for both Sales and Quantity), then aggregate.

Yours,

Yuri

2 of 2 people found this helpful
• ###### 6. Re: Aggregate Calc in Fiscal YTD Price Per Unit

Thanks so much Yuriy! I like the way how you made it simple.

• ###### 7. Re: Aggregate Calc in Fiscal YTD Price Per Unit

KK, you're welcome.