Compare rows in a table by date

[florinaturlea]

Hello,

I have the following big table:

SiteID	Date
A	10.05.2023
D	10.05.2023
A	09.05.2023
B	09.05.2023
C	09.05.2023

I want to show as KPIs: how many sites have disappeared from 09.05 to 10.05 (the result would be C, so 1), and how many sites are new in 10.05 compared to 09.05 (the result would be D, so 1).  At the same time, it is required to have a table showing the results, D and C. 

I need to, somehow, compare the rows grouped by date.  Also, the "Date" column will be selected from a list of dates, so the comparison should be dynamic.

Thank you in advance for your ideas! 

[Kushal_Chawda]

@florinaturlea  In how many sites disappeared  shouldn't it be B & C?

[edwin]

to compare two dates and look for the site present in one date but not the other you can use this:

this is looking for sites in 10/05 but not in 09/05

the converse is as follows:

to make it dynamic, you may want the user to have 2 filters one for date1 and another for date2 and build 2 variables vDate1, VDate2 and substitte the hard coded dates:

=count({<Date={'$(vDate1)'}, Site=e({<Date={'$(vDate2)'}>}Site)>}Site)

[Kushal_Chawda · Accepted solution]

@florinaturlea   You may need to ask user to select two dates from Calendar object or Date filter. With my expression, by default will be comparison between max date and second max date. Try below

Data:
Load * Inline [
SiteID Date
A 11.05.2023
B 11.05.2023
E 11.05.2023
A 10.05.2023
D 10.05.2023
A 09.05.2023
B 09.05.2023
C 09.05.2023 ](delimiter is '\t');

// Expression for Disappeared site

=Count({<SiteID = p({<Date={"$(=date(max(Date,2)))"}>} SiteID) - p({<Date={"$(=date(max(Date)))"}>} SiteID)>} distinct SiteID)

// Expression for New Sites

=Count({<SiteID = p({<Date={"$(=date(max(Date)))"}>} SiteID) - p({<Date={"$(=date(max(Date,2)))"}>} SiteID)>} distinct SiteID)

// Combined Expression to use in table

=Count({<SiteID = p({<Date={"$(=date(max(Date,2)))"}>} SiteID) - p({<Date={"$(=date(max(Date)))"}>} SiteID)> +
<SiteID = p({<Date={"$(=date(max(Date)))"}>} SiteID) - p({<Date={"$(=date(max(Date,2)))"}>}SiteID)>} distinct SiteID)

[florinaturlea]

Indeed, you are right!