topic Re: stock per period in QlikView

stock per period

lucarizziero — Wed, 20 Jun 2018 08:33:50 GMT

Hi Community,

I have warehouse data to analyze for getting the stock situation per period and product type.

I obtain the stock column using the RANGESUM function, but I need the subtotals too.

Furthermore, if I filter data (for instance the year-month) the column will not preserve the data !!!

Thanks in advance: attached the app and the data.

Re: stock per period

YoussefBelloum — Wed, 20 Jun 2018 08:46:56 GMT

Hi,

try this as Stock expression:

=sum(aggr(RANGESUM(ABOVE(TOTAL SUM([Sell-IN]+[Sell-OUT]),0,ROWNO())),Type,Line,YearMonth,Gender))

Re: stock per period

lucarizziero — Wed, 20 Jun 2018 09:20:17 GMT

Hi Youssef,

It doesn't works: furthermore my stock expression sums the Product Type stock while I need to maintain the stock separated.

I should obtain something like:

Re: stock per period

enriquem — Wed, 20 Jun 2018 09:48:08 GMT

Maybe this can help you.

I have the next table, with the expression:

Column(3)

If(isNull(Above(Column(4))),

Above(Column(4))

)

In the 4 row.

Hope it helps.

Re: stock per period

YoussefBelloum — Wed, 20 Jun 2018 09:49:46 GMT

Maybe stalwar1‌ can take a look here ?

Re: stock per period

sunny_talwar — Wed, 20 Jun 2018 10:38:19 GMT

May be this

Sum(Aggr(RangeSum(Above(Sum([Sell-IN]+[Sell-OUT]),0,RowNo())), Gender, Line, Type, YearMonth))

Re: stock per period

sunny_talwar — Wed, 20 Jun 2018 10:44:39 GMT

I guess only thing this is missing is the ability to add a row when it doesn't exist... for example Gender = MAN, year month = 201703, Line = LINE2 and Type = 6 doesn't exists, but you want to add the accumulation from 201702. This can only work if the above combination at least exists in the database (may be with a 0 value)

Re: stock per period

YoussefBelloum — Wed, 20 Jun 2018 11:37:16 GMT

So here the fields order in the AGGR() function change everything ?

Re: stock per period

sunny_talwar — Wed, 20 Jun 2018 12:30:33 GMT

Yes, it is the Aggr() dimension order that determines what needs to be accumulated rather than the chart dimension order

Re: stock per period

YoussefBelloum — Wed, 20 Jun 2018 12:35:55 GMT

Thanks