Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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))"}>)
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
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))"}>)
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Luca1
Contributor III
Contributor III
Author

Thank you so much, this was my mistake!