
1. Re: Using an calculated field in YTD calculations
Sreekanth Kasaraneni Nov 17, 2016 3:45 AM (in response to Eleonor Hellblom)1 of 1 people found this helpfulHi Eleonor,
please check the below link, you will find all calculations.
https://interworks.co.uk/blog/datecomparisonstableaumadeeasy/
Thanks,
Sreekanth.

2. Re: Using an calculated field in YTD calculations
Chris Beck Nov 17, 2016 4:50 AM (in response to Sreekanth Kasaraneni)1 of 1 people found this helpfulthat error message is telling you everything you need to know. in calculations you cannot mix aggregate and nonaggregate 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 unaggregate the right side. i vote unaggregating 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)
}

3. Re: Using an calculated field in YTD calculations
Eleonor Hellblom Nov 17, 2016 5:48 AM (in response to Chris Beck)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
Chris Beck Nov 17, 2016 6:20 AM (in response to Eleonor Hellblom)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
Eleonor Hellblom Nov 17, 2016 6:36 AM (in response to Chris Beck)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
Chris Beck Nov 17, 2016 6:54 AM (in response to Eleonor Hellblom)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
Eleonor Hellblom Nov 17, 2016 7:13 AM (in response to Chris Beck)Chris  you are an absolute star! Thank you so much for the help!