Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
M_Jay
Contributor II
Contributor II

R12 Calculation for more than one products

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)

M_Jay_0-1601820578250.png

 

Appreciate any help.

5 Replies
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
Contributor II
Contributor II
Author

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.

M_Jay_0-1601855490964.png

But this is what I am expecting:

M_Jay_1-1601855565542.png

 

Steven_Haught
Creator III
Creator III

@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
Contributor II
Contributor II
Author

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.