Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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
Former Employee
Former 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.