Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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
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
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
thanks again Sunny! Working perfectly