Compare Year to Date over multiple years in Pivot Chart
I need to compare year to date sales over multiple years. I can get it to work for totals, but I cannot get it when I need to drill down to lower levels. Something is wrong in my syntax and I cannot figure it out.
In English, I need:
On November 5, 2021 show me the total dollars of all X Brand Tee Shirts order amounts for January 1-November 5 in one column, total dollars of all X Brand Tee Shirts order amounts for January 1-November 5, 2020 in another column, and all X Brand Tee Shirts order amounts for January 1-November 5 in a third column. (I actually need to go back to 2015 but for example, we'll stop there.)
Brand Type is an existing data point not shown here. It's not a dimension at the moment.
I can get year to date of all orders
= sum (if DayNumberofYear (OrderDate) <= DayNumberofYear (today())Amount)
Or I can get the total year (January to December for 2020 and earlier, YTD for 2021) of orders by category
= Sum ({<SalesCategory = {'Clothing', 'Shoes', 'Accessories'}, Brand Type = {'X'} , OrderYear = {">2014"}>}OrderAmount)
But as soon as I try to combine these two statements into any variation of
It breaks. Changing around orders, brackets/quotes, counting parentheses has not given me an expression that gives me what I need when I try to limit by 3 data points (Category, Brand, Year to Date)
LIMITS: I do not have rights to change the script at all and add previous year flags. I can create variables, but there's got to be a way without creating a variable for each of the last 6 years I need to report on.