7 Replies Latest reply on Nov 17, 2016 7:13 AM by Eleonor Hellblom

Using an calculated field in YTD calculations

Hi,

I want to calculate something by YTD and also be able to compare it to last years YTD.

To do this I am using this function:

But as you can see I get an error. If I replace 'Fixed Calculation' to 'Sales' (in Superstore) it works. How can I do this with a calculated field?

It doesn't have to be this YTD calculation, so please if you have another way to calculate YTD then let me know!

This is the 'Fixed Calculation':

See attached workbook as well

• 1. Re: Using an calculated field in YTD calculations

Hi Eleonor,

Thanks,

Sreekanth.

1 of 1 people found this helpful
• 2. Re: Using an calculated field in YTD calculations

that error message is telling you everything you need to know. in calculations you cannot mix aggregate and non-aggregate fields. the left side of your if statement, the logical expression, is not aggregated. but the right side is - fixed calculation is an aggregate as it uses sum in the syntax. so to fix this, we need to either set the logical expression as an aggregate with an attr() function or we need to un-aggregate the right side. i vote un-aggregating the right side.

change the calculation to something like this. most individuals will create a YTD filter and set it to true on the filter shelf. this would handle the first half of the calculation below.

IF YEAR([Order Date])=YEAR(TODAY()) and MONTH([Order Date])<=MONTH(TODAY())

THEN [Sales]

ELSE 0

END

/

{

sum(IF YEAR([Order Date])=YEAR(TODAY()) and MONTH([Order Date])<=MONTH(TODAY())

THEN [Sales]

ELSE 0

END)

}

1 of 1 people found this helpful
• 3. Re: Using an calculated field in YTD calculations

Hi Chris,

Thank you for responding.

Sorry if this is a stupid question, but how will I use that calculation with the 'Fixed Calculation' in the sample worksheet I attached? I need that calculation to be in it as that is the measure my client is using - so that calculation instead of 'Sales'.

Cheers

• 4. Re: Using an calculated field in YTD calculations

here is the calc referencing your date parameter

IF

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

AND

MONTH([Order Date])<=MONTH([Reference Date])

THEN

[Sales]

END

/

{ //place your fixed calc here

sum(IF

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

AND

MONTH([Order Date])<=MONTH([Reference Date])

THEN [Sales]

ELSE 0

END)

}

• 5. Re: Using an calculated field in YTD calculations

Thank you again for trying Chris, but I am still struggling to understand where I can insert my measure calculation:

(sum([Sales])/sum( { FIXED [Country], [Category], [Order Date] : sum([Sales]) }))

in that formula without getting the same issue with the aggregation?

• 6. Re: Using an calculated field in YTD calculations

your calculation would be the below:

sum(IF

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

AND

MONTH([Order Date])<=MONTH([Reference Date])

THEN

[Sales]

END)

/

sum({ FIXED [Country], [Category], [Order Date]  :

sum(IF

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

AND

MONTH([Order Date])<=MONTH([Reference Date])

THEN [Sales]

ELSE 0

END)

})

• 7. Re: Using an calculated field in YTD calculations

Chris - you are an absolute star! Thank you so much for the help!