topic Re: Set Analysis in QlikView

Set Analysis

bellesol — Fri, 15 Nov 2024 23:23:30 GMT

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?

Re: Set Analysis

bellesol — Sat, 02 Jan 2021 18:07:21 GMT

Hi, can someone please help ?:)

Re: Set Analysis

QFabian — Sat, 02 Jan 2021 19:30:40 GMT

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

Re: Set Analysis

Vegar — Sat, 02 Jan 2021 19:48:02 GMT

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 .

Re: Set Analysis

bellesol — Tue, 05 Jan 2021 11:00:18 GMT

Thank you guys !