Qlik Community

Qlik Sense App Development

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

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Set Analysis Accumulation Not Working

Hello,

I have the following "As of" expression that is not showing the accumulation of the payment amount after the period when the payment first happened in 2012-Dec.  The set analysis syntax has a search parameter to include all payments on or before the  Max([Year Month]).  In this case the Max([Year Month]) is 2018-May.  

Expression: Sum({<Year=,Month=,[Year Month]={"<= $(=Max([Year Month]))"}>} PaymentAmount) 

See attached screen shot of a table showing the payments.  The periods following 2012-Dec are zero, but should be 1,891.87. 

Any information to resolve this issue would be greatly appreciated!  

Thank you, 

Labels (2)
1 Solution

Accepted Solutions
Highlighted

Are you looking for this may be

RangeSum(Above(
Sum({<Year=,Month=,[Year Month]={"<=$(=Max([Year Month]))"}>} PaymentAmount)
, 0, RowNo()))

View solution in original post

5 Replies
Highlighted
Employee
Employee

The Max aggregation is for numeric fields, the Year month is a text string, so max will not work here

Highlighted
MVP
MVP

It's better to deal with the string date fields in the script. However, if that is not in the scope, you could try parsing the string in set analysis itself like:

 

Sum({<Year=,Month=,[Year Month]={"=Date#([Year Month], 'MMM-YYYY') <=
$(=Max(Date#([Year Month],'MMM-YYYY')))"}>} PaymentAmount)
Highlighted
Partner
Partner

Hi Lisa_P,

Thank you for the help!  

The expression I am using is numeric.  However, to confirm if the "Year Month" was the issue, I changed the expression to the following.  The change did not correct the issue.  Please see attached screen shot of the table using the new expression.  

Expression: Sum({<Year=,Month=,[_MonthSerial]={"<= $(=Max([_MonthSerial]))"}>} PaymentAmount)

I would have thought the "< =" syntax in the expression would be able to show the balance forward to all future periods after the initial posting in Dec-2012.    

Highlighted

Are you looking for this may be

RangeSum(Above(
Sum({<Year=,Month=,[Year Month]={"<=$(=Max([Year Month]))"}>} PaymentAmount)
, 0, RowNo()))

View solution in original post

Highlighted
Partner
Partner

Thanks Sunny!!