R12 Calculation for more than one products

[M_Jay]

Hi,

I have 2 tables - 1) Invoice table 2) Date. They are connected by Date as below. 

And there are more than 100s of products in the "Invoice Table". And some products may not have sales in some months.

Since there are lot of products the BILL_DATE has all dates ( missing some for some products). The reports are good. Now I am trying to do Rolling 12 Months calculation for each product. I am using the below formula. It gives the correct R12 value if there is no missing months. But if there is missing months it looks for 12 sales months instead of last 12 months. 

aggr(rangesum(above(Sum(Quantity),0,12)),product, FISCAL_Month)

Appreciate any help.

[Kushal_Chawda]

@M_Jay  You have two options

1) You many need to fill the missing dates in your invoice table so that you will have all the Dates for each product and then your expression will return the correct value. See the below links for this option

https://community.qlik.com/t5/Qlik-Design-Blog/How-to-populate-a-sparsely-populated-field/ba-p/14706...

https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

2) You can create AsOfTable which you can link to your calendar 

https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

[Kushal_Chawda]

@M_Jay  or you can try below

sum(aggr(rangesum(above(Sum(Quantity)+sum({1}0),0,12)),product, FISCAL_Month))

[M_Jay]

Thanks for your response Kush. But none of them are working. Already Bill Date has all dates. Since there are more than 100s of products there is a sale on each date. The problem is I am trying to do R12M calculation by each product which may not have sales in all months. This is what I am getting.

But this is what I am expecting:

[Steven_Haught]

@M_Jay 

Kush is right that you will have to create the date or a 0 value sale for each product/Date. Recently had to do this with sales and unit closures during covid in order to get a proper rolling average. Was the same issue where I had to create a date point and a sale point, even if it is now a 0, for each individual Unit so that I could roll that up. We did this using a master calendar to create any missing dates and then replaced any null sales points with a 0.  

[M_Jay]

Thanks Steven for your response. In that case I have to set up the sales numbers as 0 for missing months in my data model. I will try.