How to pivot my data and get counts based on the min and max date value

Question

Morning Sisense community, I need to pivot my data and get counts based on the min and max date value. Given the below data: row date count Example 1/01/2025 35 Example 2 1/01/2025 20 Example 1/02/2025 35 Example 2 1/02/2025 10 Example 1/04/2025 34 Example 2 1/04/2025 10 Example 1/08/2025 34 Example 2 1/08/2025 10 I want to pivot it to: Row Start Count End Count Example 35 34 Example 2 20 10 I need to be able to filter my data in the dashboard. So, if the date is filter to “1/04/2025” to “1/08/2...

steve · Accepted Answer

I haven't fully validated this, but on first glimpse it seems to work (if I understood correctly)For the pivot 'values' you can use a filter within a formula using the syntax documented hereOn [Days in Date] click 'Edit Filter' to apply a Bottom 1 rank filter on MIN([Days in Date])Duplicate the value for "End Date", and change the filter to be Top 1 rank filter on MAX([Days in Date])Check your numbers match what you expect 😀