Availability of an item

[AdnanMirza]

Hi,

I have a table consisting of the columns Item, Event_date, Quantity.

A sample is shown below:

TabA:

Load * Inline [

Item, Event_date, Quantity

Tent,  09/04/2019, 5

Tent,  10/04/2019, 5

Tent,  11/04/2019, 42

Tent,  12/04/2019, 42

Tent,  13/04/2019, 37

];

So the Total quantity used is 131.

I want to see the quantity available on the date 08/04/2019 (total - used) on the linear chart

when I choose date as a dimension and quantity as a measure and I choose Tent as a selection.

I am able to add dummy dates to the table and for each dummy date the quantity used as 0 but I

cannot figure out how to add the item for each dummy date from the script.

Is it the way it shoud be done? And if so then please help me figure out how.

Thanks in advance.

[JGMDataAnalysis]

I hope this gives you an idea of ​​how to solve the problem.

Script:

// I changed the data to create gaps between dates.
// I also added one more item to enrich the example.
// In addition, I considered the possibility of duplications 
// such as row 2 of the inline (same Item and same date).

Temp:
NOCONCATENATE
LOAD *, Date(Date#(Event_date, 'DD/MM/YYYY')) AS tempEvent_date
INLINE [
    Item,   Event_date, Quantity
    Tent,   09/04/2019, 5
    Tent,   09/04/2019, 7
    Tent,   10/04/2019, 5
    Tent,   12/04/2019, 42
    Tent,   13/04/2019, 42
    Tent,   22/04/2019, 37
    Tent 1, 30/03/2019, 2
    Tent 1, 15/04/2019, 7
    Tent 1, 16/04/2019, 15
    Tent 1, 25/04/2019, 32
    Tent 1, 02/05/2019, 45
];

DROP FIELD Event_date;


// I calculate the amount accumulated for each record.

Temp1:
NOCONCATENATE
LOAD *, 
	 If(Item = Peek(Item),
     		Quantity + Peek(Accum_Quantity), Quantity) AS Accum_Quantity
RESIDENT Temp
ORDER BY Item, tempEvent_date;


// I calculate the top date for each record to define 
// the interval for which I have to fill in the gaps.
// If it's the last record of the item, I assign today's date.

Temp2:
NOCONCATENATE
LOAD *,
	 Date(If(Item = Peek(Item),
     	Peek(tempEvent_date) + (tempEvent_date <> Peek(tempEvent_date)),
     		Today())) AS tempToDate
RESIDENT Temp1
ORDER BY Item, tempEvent_date DESC;


// I generate records to complete the gaps.
// As I do not know the actual data that you are going to use, 
// you will have to evaluate whether the amount of records generated 
// by this step is too large consider the possibility of making monthly.

Test:
NOCONCATENATE
LOAD Item,
	 Date(tempEvent_date + IterNo() - 1) AS Event_date,
     If(IterNo() = 1, Quantity, 0) AS Quantity,
     Accum_Quantity
RESIDENT Temp2
WHILE (tempEvent_date + IterNo() - 1) <= tempToDate;

DROP TABLES Temp, Temp1, Temp2;

Measure:

Result obtained without applying any filter:

Result obtained by filtering Item = Tent

Result obtained by filtering Item = Tent and  Event_day = 15/04/2019