Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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