Set Analysis

[bellesol]

Hi,

I would Like to create a graph that display - how much inventory was enter the storage and how much inventory was exit the storage - by months.

The dimension is month enter

so the first expression that calculate how much inventory entered the storage - sum([Total Value $])

how do I calculate how much inventory was exit the storage?

I would like to display it in one graph,

I created dummy data and two graph that I would like to combine to one, I would like to add the expression of how much inventory was out of the storage to the first graph.:

LOAD * INLINE [
Enter Date, Month Enter Date, Date OUT,Month Date OUT, Total Value $
21/10/2020,10, 04/11/2020,4, 361
15/01/2020,01, 19/01/2020,01, 687
28/10/2020,10, 16/11/2020,11, 397
15/01/2020,01, 19/01/2020,01, "2,822"
28/10/2020,10, 16/11/2020,11, 577
05/08/2020,08, 04/11/2020,11, "1,151"
05/08/2020,08, 04/11/2020,11, 413
15/01/2020,01, 19/01/2020,01, "3,962"
11/11/2020,11, 16/11/2020,11, 849
22/01/2020,01, 20/08/2020,08, 103
04/11/2020,11, 16/11/2020,11, 83
15/01/2020,01, 19/01/2020,01, 163
08/01/2020,01, 19/01/2020,01, 56
];

How do I combine this two graph into one?

[bellesol]

Hi, can someone please help ?:)

[QFabian]

Hi @bellesol , please try this script, it takes your data, then separeted to assign a type of movement

Data:
LOAD * INLINE [
EnterDate, MonthEnterDate, DateOUT,MonthDateOUT, TotalValue$
21/10/2020,10, 04/11/2020,4, 361
15/01/2020,01, 19/01/2020,01, 687
28/10/2020,10, 16/11/2020,11, 397
15/01/2020,01, 19/01/2020,01, "2,822"
28/10/2020,10, 16/11/2020,11, 577
05/08/2020,08, 04/11/2020,11, "1,151"
05/08/2020,08, 04/11/2020,11, 413
15/01/2020,01, 19/01/2020,01, "3,962"
11/11/2020,11, 16/11/2020,11, 849
22/01/2020,01, 20/08/2020,08, 103
04/11/2020,11, 16/11/2020,11, 83
15/01/2020,01, 19/01/2020,01, 163
08/01/2020,01, 19/01/2020,01, 56
];

Load
'In' as Mov,
EnterDate as Date,
MonthEnterDate as Month,
TotalValue$
REsident Data;

Load
'Out' as Mov,
DateOUT as Date,
MonthDateOUT as Month,
TotalValue$
REsident Data;

drop table Data;

Then i create a stacked bar

check the qvw

[Vegar · Accepted solution]

Set analysis might not be the best tool to fix this. You will need to create a common date/month-dimension. I suggest a calendar-link-dimension  connection to  a master calendar.

OBS: You need to have [Calendar] as a dimension or specified in a set like this : sum({<Calendar={'OUT'}>}[Total Value $])

 See script sample below. 

Transactions:
LOAD 
	AutoNumberHash128([Enter Date], [Date OUT]) as %calendarlinkID,
	[Total Value $],
	[Enter Date],
	[Date OUT]
INLINE [
Enter Date, Month Enter Date, Date OUT,Month Date OUT, Total Value $
21/10/2020,10, 04/11/2020,4, 361
15/01/2020,01, 19/01/2020,01, 687
28/10/2020,10, 16/11/2020,11, 397
15/01/2020,01, 19/01/2020,01, "2,822"
28/10/2020,10, 16/11/2020,11, 577
05/08/2020,08, 04/11/2020,11, "1,151"
05/08/2020,08, 04/11/2020,11, 413
15/01/2020,01, 19/01/2020,01, "3,962"
11/11/2020,11, 16/11/2020,11, 849
22/01/2020,01, 20/08/2020,08, 103
04/11/2020,11, 16/11/2020,11, 83
15/01/2020,01, 19/01/2020,01, 163
08/01/2020,01, 19/01/2020,01, 56
];


CalendarLink:
LOAD 
	%calendarlinkID,
	[Enter Date] as %Date,
	'Enter' as Calendar
Resident Transactions;
LOAD 
	%calendarlinkID,
	[Date OUT] as %Date,
	'OUT' as Calendar
Resident Transactions;

DROP FIELD [Date OUT], [Enter Date]; //I no longer need these two date fields in the transaction table;

/**
Generate a master calendar for all %Date values in the Calendar Link table. 
*/
LET vEnd= '31/12/2020';
LET vStart = '01/01/2020';

MasterCalendar:
Load %Date, 
	Month(%Date) as Month,
	NUM(Month(%Date)) as MontNum;
Load Date('$(vStart)' + RecNo()) as %Date autogenerate '$(vEnd)' - '$(vStart)' ;

A good read  is HIC's old blog post about Canonical Date .

[bellesol]

Thank you guys !