2

My table has the stock movement for each item by day. So at any point if I want to find the current stock, I need to sum all the stock movements from the day business started.

Now I want to create a report which shows the stock level over a period of time. The period can be selected by the user.

Here is a simplified version of my table:

Stock:

LOAD * INLINE

[Brand,Item,Reason,Date,StockMovement
EveLom,E001,OpeningStock,01/01/2014,200
EveLom,E001,Sale,05/01/2014,-100
EveLom,E001,Purchase,01/03/2014,100
EveLom,E001,Sale,01/05/2014,-50
EveLom,E001,Sale,01/10/2014,-50
EveLom,E001,Sale,21/10/2014,-20
EveLom,E001,Sale,01/01/2015,-30
EveLom,E001,Sale,01/03/2015,-10
EveLom,E001,Purchase,01/04/2014,100
EveLom,E002,OpeningStock,01/01/2014,2000
EveLom,E002,Sale,05/01/2014,-100
EveLom,E002,Purchase,01/03/2014,100
EveLom,E002,Sale,01/05/2014,-50
EveLom,E002,Sale,01/10/2014,-50
EveLom,E002,Sale,01/11/2014,-20
EveLom,E002,Sale,01/01/2015,-30
EveLom,E002,Sale,01/02/2015,-10
EveLom,E002,Purchase,01/03/2014,100
LQ,L001,OpeningStock,01/01/2014,1000
LQ,L001,Sale,05/01/2014,-100
LQ,L001,Purchase,01/03/2014,100
LQ,L001,Sale,01/05/2014,-50
LQ,L001,Sale,01/10/2014,-50
LQ,L001,Sale,21/10/2014,-20
LQ,L001,Sale,01/01/2015,-30
LQ,L001,Sale,01/03/2015,-10
LQ,L001,Purchase,01/04/2014,100
LQ,L002,OpeningStock,01/01/2014,5000
LQ,L002,Sale,05/01/2014,-100
LQ,L002,Purchase,01/03/2014,100
LQ,L002,Sale,01/05/2014,-500
LQ,L002,Sale,01/10/2014,-500
LQ,L002,Sale,01/11/2014,-200
LQ,L002,Sale,01/01/2015,-300
LQ,L002,Sale,01/02/2015,-100
LQ,L002,Purchase,01/03/2014,100
];

There are four items and they are grouped under 2 brands.

Now I want a stacked bar chart for Stock Level by Brand Vs Month.

The chart should look like below. Months will be in X-Axis.

Stock will be in Y-Axis.

I tried several methods without any success. Any ideas/guidance will be much appreciated.

See qlik community thread here.

Kara
  • 6,115
  • 16
  • 50
  • 57
Mayuran Parathalingam
  • 41
  • 8
  • An answer was provided to your thread on the Qlik Community (https://community.qlik.com/message/757720#757720) - does this answer your question? If so, please update your question and perhaps add the thread answer as a solution to your question here. – i_saw_drones Apr 21 '15 at 09:02
  • @i_saw_drones not sure if the user will return, added answer in post to try and keep the QV section of stack clean as it grows – Matt Apr 23 '15 at 12:31

1 Answers1

0

Pulled from the Qlik community

Expression:

if(YearMonth=Min(TOTAL YearMonth), 
sum(TOTAL <Brand> {<YearMonth={"<=$(=Min(YearMonth))"}, Year, Month>} StockMovement),
sum(StockMovement))
Matt
  • 14,906
  • 27
  • 99
  • 149