Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
lucarizziero
Partner - Contributor III
Partner - Contributor III

stock per period

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.

9 Replies
YoussefBelloum
Champion
Champion

Hi,

try this as Stock expression:

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

lucarizziero
Partner - Contributor III
Partner - Contributor III
Author

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:

enriquem
Creator
Creator

Maybe this can help you.

I have the next table, with the expression:

     Column(3)

          +

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

          0,

          Above(Column(4))

     )

In the 4 row.

Captura.PNG

Hope it helps.

Kind Regards,
Enrique Mora.
YoussefBelloum
Champion
Champion

Maybe stalwar1‌ can take a look here ?

sunny_talwar

May be this

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


Capture.PNG

sunny_talwar

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)

YoussefBelloum
Champion
Champion

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

sunny_talwar

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

YoussefBelloum
Champion
Champion

Thanks