Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
medempudi_ravi
Contributor II
Contributor II

How to show sum for previous 3 months in one coulmn and sum of corresponding month in another column of a straight table?

Hi Community,

I have a situation where I need to create a straight table where,

  • Dimension is Month End field where I need to show Trailing 10 months
  • In first expression, I am showing sum(Field X)  which shows the corresponding sum grouped by MonthEnd.

In the next column of the table, I Need to show Sum(Field X) for Previous 3 Month Ends. Can you help me write the expression for this column.

Please find the attached excel which shows example of my requirement.

Thanks in Advance.


1 Solution

Accepted Solutions
medempudi_ravi
Contributor II
Contributor II
Author

Hi Bikash,

I could successfully solve the problem I have defined using the formula similar to one below:

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month))


Hope this is helpful for you:)


Thanks

View solution in original post

3 Replies
MK_QSL
MVP
MVP

Try to make yourself reading below link... Very useful...

Calculating rolling n-period totals, averages or other aggregations

Not applicable

Hello Manish, the link that you have shared is for Rolling n-periods, and not trailing. And what I have understood from the problem statement defined by Raviteja is that, he doesn't want a cumulative total for last 3 months, rather I think he wants something like below for which even I am looking for a solution:

Say our data looks like this:

Month_Period                    Amount

Jan 2012                              100

Feb 2012                              200

Mar 2012                              150

Apr 2012                               50

May 2012                              100

Jun 2012                                50

and so our trailing 3 months graph or chart should look something like this:

Month_Period                    Trailing_Amount

Jan 2012                              100 [=100+0+0, 0's from previous months]

Feb 2012                              300 [=200+100+0]

Mar 2012                              450 [=150+200+100]

Apr 2012                              400 [=50+150+200]

May 2012                             300 [=100+50+150]

Jun 2012                               200 [=50+100+50]

Please share if you have made any progress on this problem statement.

Thanks.

medempudi_ravi
Contributor II
Contributor II
Author

Hi Bikash,

I could successfully solve the problem I have defined using the formula similar to one below:

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month))


Hope this is helpful for you:)


Thanks

View solution in original post