topic Re: Weeks on Inventory Line Chart in QlikView

Weeks on Inventory Line Chart

clondono — Wed, 25 Nov 2020 16:16:04 GMT

Hello community,

I created a straight table to calculate the Inventory AMT, Inventory QTY, Run Rate and Weeks on Inventory (WOI). I’m getting the desired results on this table.

My expressions for the table are:

INV AMT:

SUM({$<YYYYQ=, YYYYWK={$(cLastYYYYWK)}>} InvAMT)

INV QTY:

SUM({$<YYYYQ=, YYYYWK={$(cLastYYYYWK)}>} InvQTY)

RUN RATE:

(sum({<YYYYQ=, YYYWK=,TRANSACTION_YYYYWK={">$(cLastWeek_Minus_1_Qtr_YYYYWK)"}>}ResaleQTY)/13)

WOI (AMT):

(Sum({<YYYYQ=, YYYYWK={$(cLastYYYYWK)}>})InvAMT) /
(sum({<YYYYQ=, YYYYWK=, PROCESS_YYYYWK={">$(cLastWeek_Minus_1_Qtr_YYYYWK)"}>}ResaleAMT)/13)

WOI (QTY):

[Inv Qty] / [Run Rate]

I have a requirement to display a line chart for the WOI measure and it should display results for the last 26 weeks $(cLastWeek_Minus_2_Qtr_YYYYWK) – see image below for sample requirement. Any ideas on how to achieve this using the same metrics?

Thanks!

Re: Weeks on Inventory Line Chart

sushil353 — Wed, 26 Oct 2016 06:08:59 GMT

Hi,

You can create a mastercalendar in your datamodel having Weeks as desired and put that into you dimension.

to create a master calendar you can search community there are many post out there.. Master Calendar Generation Script

HTH

Re: Weeks on Inventory Line Chart

Anil_Babu_Samineni — Wed, 26 Oct 2016 06:34:11 GMT

Try Like This, Field must be there

Week(DateField) as Week,

Expression:

SUM({$<Week = {'$(=WeekStart(Week,-26))'}, YYYYQ=, YYYYWK={$(cLastYYYYWK)}>} InvQTY)

sum({<Week = {'$(=WeekStart(Week,-26))'}, YYYYQ=, YYYWK=,TRANSACTION_YYYYWK={">$(cLastWeek_Minus_1_Qtr_YYYYWK)"}>}ResaleQTY)/13

Re: Weeks on Inventory Line Chart

clondono — Wed, 26 Oct 2016 18:06:23 GMT

cbaqirdhds‌

Re: Weeks on Inventory Line Chart

cbaqir — Wed, 26 Oct 2016 21:52:51 GMT

Does Anil's solution work?

Re: Weeks on Inventory Line Chart

clondono — Thu, 27 Oct 2016 15:58:48 GMT

No, neither of the solutions recommended work. We already have a master calendar and a variable for the "week" field, still unable to create the line chart for Weeks on Inventory.

The app is very large in size, I'm trying to create a slim-down version so I can upload to the community.

Thanks,

Carlos

Re: Weeks on Inventory Line Chart

Anil_Babu_Samineni — Thu, 27 Oct 2016 16:02:30 GMT

That is the best approach.

Re: Weeks on Inventory Line Chart

cbaqir — Thu, 27 Oct 2016 16:31:49 GMT

Sum({<DATE={">=$(=addweeks(weekend(today()),-26))<=$(=addweeks(weekend(today()),0))"}>}InvQTY)

Sub DATE for your date field.