Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
@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 In how many sites disappeared shouldn't it be B & C?
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)
@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)
Indeed, you are right!