Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
kralj_matej
Contributor II
Contributor II

Sales for previous year - sum if

Hello. I want to show gross sales only for 2011 year by using sum and if. Can someone tell me why is this function wrong and write down the correct function? 

kralj_matej_1-1656943705175.png

 

Thank you in advance.

 

 

 

 

 

Labels (3)
1 Solution

Accepted Solutions
Or
MVP
MVP

You can't sum a sum, so unfortunately that won't work, regardless of the if() condition. You'll have to either use aggr() for the internal sum or re-write the formula to not use a nested aggregation.

View solution in original post

6 Replies
Or
MVP
MVP

This doesn't appear to be wrong, assuming [Gross sales] is a field in your application. Note that this will not ignore selections on any fields, so it will only work if no selections have been made or the selections made do not interfere with the sum. If Gross sales is an existing measure, this may not work because of a nested aggregation.

Note that this would often be written using set analysis instead:

sum({< [OrderDate.autoCalendar.Year] = {2011} >} ] [Gross sales])

Which would ignore any selections made on the Year field and always apply 2011 instead.

hic
Employee
Employee

Try
Sum(If([OrderDate.autoCalendar.Year] = Date(MakeDate(2011),'YYYY'), [Gross sales], 0)

The reason is that the "OrderDate.autoCalendar.Year" is really a date (Jan 1), but formatted as a Year...

kralj_matej
Contributor II
Contributor II
Author

Thank you for quick response. I forgot to write down that "Gross sales" is measure (sum=unitPrice*quantity). 

There is still error:

kralj_matej_0-1656944587082.png

 

kralj_matej
Contributor II
Contributor II
Author

Thank you for quick response. I forgot to write down that "Gross sales" is measure (sum=unitPrice*quantity). 

There is still error:

kralj_matej_1-1656944652909.png

 

 

Or
MVP
MVP

You can't sum a sum, so unfortunately that won't work, regardless of the if() condition. You'll have to either use aggr() for the internal sum or re-write the formula to not use a nested aggregation.

kralj_matej
Contributor II
Contributor II
Author

Ok. Thank you for help! Have a nice day.