Calculate cumulative field with compare prev month data

[febridwi]

Hi Everyone,

I have a sample case, it's Sales data, for AT (Activity of Transaction) I can do but for calculating RO(Registration of Outlet) I feel stuck. In detail, RO is calculated cumulatively from the beginning of Jan
with term: **calculated as an outlet if one month has transacted> 0
example:
Jakarta: Outlet 8 is still calculated because in Jan> 0, even though it is cumulative Jan - Feb <0

I put the qvw &  the data excel in below. And the result from the RO must be like in the table as manual calculation.

BRANCH	JAN	FEB
JAKARTA	7	13
SURABAYA	6	10

Does anyone ever do the same thing? Does anyone have an idea?

Have a nice day! Thank you

Febri.

[sunny_talwar · Accepted solution]

Best way to handle this is to use AsOfTable

Script

Table:
LOAD [DATE TRANS], 
	 Date(MonthStart([DATE TRANS]), 'MMM-YYYY') as MonthYear,
     MONTH AS BULAN, 
     BRANCH, 
     OUTLET, 
     QTY
FROM
[Sample Data (1).xlsx]
(ooxml, embedded labels, table is DATA);

AsOfTable:
LOAD DISTINCT MonthYear as AsOfMonthYear,
	 Month(MonthYear) as AsOfMonth,
	 Date(MonthStart(MonthYear, -IterNo() + 1), 'MMM-YYYY') as MonthYear,
	 -IterNo() + 1 as Flag
Resident Table
While MonthStart(MonthYear, -IterNo() + 1) >= YearStart(MonthYear);

Pivot table Dimensions

BRANCH
AsOfMonth

Expression

Sum(Aggr(If(Max(TOTAL <BRANCH, OUTLET, AsOfMonth> Aggr(Sum(QTY), BRANCH, OUTLET, AsOfMonth, BULAN)) > 0, 1, 0), OUTLET, AsOfMonth))

[febridwi]

Hi, @sunny_talwar Thank you for your reply. I checked it works & corrects  

In another way, what if the started month of RO put as a parameter? Because Month will increase, do you have any suggestion? 

Thanks a lot.

[sunny_talwar]

---

@febridwi wrote:

In another way, what if the started month of RO put as a parameter? Because Month will increase, do you have any suggestion? 

---

I am not sure I follow your question. Would you be able to elaborate a little?

[febridwi]

Hi @sunny_talwar sorry for replying late. I was asking, what if the calculation of RO not only from Jan until end. But I can change the month start to calculate RO, ex: the month in data Jan-Feb, but I want to calculate from Feb.  

[sunny_talwar]

I am still not 100% sure I understand, but what would be the output (numerically) if you calculate from Feb?

[febridwi]

well, unfortunately, I don't have yet the example. sorry. But I think it will use further. 
If you don't mind @sunny_talwar would you give me an advice with my other case here

Thank you, have a great day!