topic Re: R12 Calculation for more than one products in Visualization and Usability

R12 Calculation for more than one products

M_Jay — Sun, 04 Oct 2020 14:11:43 GMT

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.

Re: R12 Calculation for more than one products

Kushal_Chawda — Sun, 04 Oct 2020 20:48:14 GMT

@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/1470637

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

Re: R12 Calculation for more than one products

Kushal_Chawda — Sun, 04 Oct 2020 21:08:28 GMT

@M_Jay or you can try below

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

Re: R12 Calculation for more than one products

M_Jay — Sun, 04 Oct 2020 23:53:01 GMT

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:

Re: R12 Calculation for more than one products

Steven_Haught — Mon, 05 Oct 2020 13:30:30 GMT

@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.

Re: R12 Calculation for more than one products

M_Jay — Mon, 05 Oct 2020 22:02:28 GMT

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.