Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Arein00
Contributor
Contributor

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. 

Arein00_1-1636148622086.png

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

= Sum (if DayNumberofYear (OrderDate) <= DayNumberofYear (today())({<SalesCategory = {'Clothing', 'Shoes', 'Accessories'}, Brand Type = {'X'} , OrderYear = {">2014"}>} Amount)

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.

0 Replies