Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Emil_bros
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
sunny_talwar

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
sunny_talwar

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

Emil_bros
Contributor II
Contributor II
Author

thanks again Sunny! Working perfectly