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: 
Luca1
Contributor III
Contributor III

Sum for DateRange

I want to sum up all Sales that are further in the past than 365 days. However, my current code returns 0. I suspect that there are some formatting issues with the max() function and the YearMonth format, but I'm not sure about that. Thank you for your help!

=sum(
      {
            <[OrderDate.autoCalendar.YearMonth]
            = {'< max([OrderDate.autoCalendar.YearMonth]) - 365'}
            >
      }
Sales
)

Labels (4)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

sum({<[OrderDate.autoCalendar.Year]={"=max(OrderDate.autoCalendar.Year)"}>)

You need to dollarexpand your expression in order to get the expression above to to work. Try changing it as I did below.

sum({<[OrderDate.autoCalendar.Year]={"$(=max(OrderDate.autoCalendar.Year))"}>)

View solution in original post

7 Replies
Digvijay_Singh

Try this may be, assuming YearMonth is of type date.

=sum(
      {
            <[OrderDate.autoCalendar.YearMonth]
            = {<$(=max([OrderDate.autoCalendar.YearMonth]) - 365)}
            >
      }
Sales
)

Luca1
Contributor III
Contributor III
Author

Hey, unfortunately this didn't help - I'm still getting 0 as a result even though that shouldn't be the case. But I appreciate your support!

Digvijay_Singh

Can you see at the bottom of the expression editor, normally I correct my formatting by looking at what I see over there...

Digvijay_Singh_0-1640048613874.png

 

Vegar
MVP
MVP

Could it be that you are making selections that are in conflict with your set expression?

What happens if you only make selections in the [OrderDate.autoCalendar.YearMonth] field?

You could also try to put double quotes around your modifier like I've done in my expression below.

=sum({< [OrderDate.autoCalendar.YearMonth] =  {"< $(=addyears(max([OrderDate.autoCalendar.YearMonth]),-1))"}>}Sales)

Luca1
Contributor III
Contributor III
Author

Your suggestion doesn't work neither. Let me maybe elaborate on stuff that I've tried and behavior that I could observe:
1. If I use 

=sum({<[OrderDate.autoCalendar.Year]={"2017"}>} Sales)

 the output will be reasonable. 

2. If try to achieve the same using

sum({<[OrderDate.autoCalendar.Year]={"=max(OrderDate.autoCalendar.Year)"}>)

the output is 0.

3. However, if I put 

=max([OrderDate.autoCalendar.Year])

to a separate KPI cell, the output is 2017 (as expected). 

Therefore, I think that you are right about that the set expression causes the error. I just don't understand what I do wrong. 

Vegar
MVP
MVP

sum({<[OrderDate.autoCalendar.Year]={"=max(OrderDate.autoCalendar.Year)"}>)

You need to dollarexpand your expression in order to get the expression above to to work. Try changing it as I did below.

sum({<[OrderDate.autoCalendar.Year]={"$(=max(OrderDate.autoCalendar.Year))"}>)
Luca1
Contributor III
Contributor III
Author

Thank you so much, this was my mistake!