topic Re: Compare rows in a table by date in App Development

Compare rows in a table by date

florinaturlea — Mon, 15 May 2023 19:02:18 GMT

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!

Re: Compare rows in a table by date

Kushal_Chawda — Mon, 15 May 2023 19:32:44 GMT

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

Re: Compare rows in a table by date

edwin — Mon, 15 May 2023 20:10:38 GMT

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)

Re: Compare rows in a table by date

Kushal_Chawda — Mon, 15 May 2023 22:33:07 GMT

@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)

Re: Compare rows in a table by date

florinaturlea — Tue, 16 May 2023 04:28:18 GMT

Indeed, you are right!