Qlik Community

App Development

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

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asmithids
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
sunny_talwar

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
Lisa_P
Employee
Employee

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

tresesco
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)
asmithids
Partner
Partner
Author

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.    

sunny_talwar

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

asmithids
Partner
Partner
Author

Thanks Sunny!!