Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Refering to other months in the formula using AFTER and ADDMONTHS fails

Hi Colleagues,

I have a simple formula to implement in pivot table:

current month stock / next month sales  * 30

and this is to be calculated for each selected month separately, also quartely cumulatuion must be active.

I have two approaches but none of it works:

1) using AFTER function failes because of month/quarter/halfyear cumulation.

Capture.PNG

where SALES NEXT MONTH  =  after(sum(Sales),1)

              DAYS = (sum(Stock) / after(sum(Sales),1) *30

function AFTER won't properly assign next month data for every 3rd month of quarter and I don't know how to solve this.

2) using AddMonths fails to return next month data:

Capture2.PNG

 where SALES NEXT MONTH =  sum({<Month = {'$(vNM)'} Sales)
              vNM = (DATE(ADDMONTHS(Month,+1),'MM-YYYY'))

with this approach I can't even get next month data.

 

Please advise how to solve this problem,

Best,

Emil

 

1 Solution

Accepted Solutions
Highlighted

Try adding TOTAL qualifier after the 'AFTER' function

After(TOTAL Sum(Sales), 1)

Although I suggest looking into  The As Of Table for this kind of requirement

View solution in original post

2 Replies
Highlighted

Try adding TOTAL qualifier after the 'AFTER' function

After(TOTAL Sum(Sales), 1)

Although I suggest looking into  The As Of Table for this kind of requirement

View solution in original post

Contributor II
Contributor II

thanks again Sunny! Working perfectly