topic Re: Calculation of Inventory Average in QlikView

Calculation of Inventory Average

vikas_nandanwar — Tue, 16 Jan 2018 10:53:48 GMT

Hi,

I have a set of data consisting of columns as Month, Week, Office, Sales and Inv.

Few points to be considered-

My period starts from 1st Jan 2017.

I need to calculate Rotation days as

(No of Days from 1st Jan) / [(Total Sales till Date)/(Average Inv till date)]

When i distribute it month wise, i don't get the average

Please help

I am attaching my app and data with this.

Thanks,

Vikas

Re: Calculation of Inventory Average

sunny_talwar — Tue, 16 Jan 2018 13:06:02 GMT

What are correct numbers that should show up on the chart? I am not sure I understand why 153 is not the right number for Jan based on the calculation you pointed out above...

Also, try not to create duplicate thread for the same issue

Calculation of rotation days

Re: Calculation of Inventory Average

vikas_nandanwar — Wed, 17 Jan 2018 05:57:40 GMT

Hi,

My Calculation should be

NO of Days from Opening * (Cumulative Average Inv / Cumulative Sales)

I've attached excel with the formulas

Yellow fields indicate Days, Avg inv and Sales

Green Column are the numbers which i am looking for.

Thanks

Re: Calculation of Inventory Average

vikas_nandanwar — Thu, 18 Jan 2018 11:19:34 GMT

Hi,

Worked with the below expression

Avg(Days)*

RangeAvg(Above(TOTAL(Avg(Aggr(Sum(Inv),Months,Week))),0,If(RowNo()<6,RowNo(),6)))/

RangeSum(Above(TOTAL(Sum(Sales)),0,If(RowNo()<12,RowNo(),12)))