How to make a date equal to another date index in set analysis

[ninawang99]

Hi All,

My dataset was like below,

Item	Import Date	Export Date
Corn	01-2019	02-2019
Book	02-2019	-
Tea	10-2020	11-2021
Bowl	11-2021	-
Plate	12-2021	02-2022
Cloths	01-2022	-
Cookie	02-2022	02-2022
Pants	02-2022	02-2022
Camera	03-2022	-

I would like to make a line chart using import_date as datetime index to check how many items are imported and exported in that MonthYear.

for example, in 01-2019, 1 imported and 0 exported, in 02-2022, 2 imported and 3 exported.

I used Imported Dated as dimension in the line chart, and count(Item) to check how many items imported, but when I try expressions like 

count( {<Export Date = >} Item) 

or  count( {<Export Date ={"$(=Import Date)"} >} Item) 

or  count( {<Export Date ={"$(=only(Import Date))"} >} Item) 

the results are not correct.

Do you have any good idea about this issue?

Thanks

Nina

[udit_k]

Hi , 

you can create Flag in script as below:-

Load *,if([Import Date]=[Export Date],1,0) as Flag;
Load * Inline [
Item,Import Date,Export Date
Corn,01-2019,02-2019
Book,02-2019,
Tea,10-2020,11-2021
Bowl,11-2021,
Plate,12-2021,02-2022
Cloths,01-2022,
Cookie,02-2022,02-2022
Pants,02-2022,02-2022
Camera,03-2022,
];

chart expression:-

count({<Flag={'1'}>}Item)

[ninawang99]

Hi,

What I would like get is to use the import date as dimension and check how many items  export in that month year, so the flag you mentioned is not correct. 

which means in the table, import date is the first column, and how many item exported is the second column, the results should be like

Import Date	Nbr of Export
01-2019	0
02-2019	1
10-2020	0
11-2021	1
12-2021	0
01-2022	1
02-2022	3
03-2022	0

[udit_k]

use below exp:-

[ninawang99]

Hi,

If I just wanna keep two columns, which are Import Date and Number of Export, is there any appropriate expression can be applied?

Many thanks

[udit_k]

use expression:-