How to pivot my data and get counts based on the min and max date value
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/2025” in the dashboard, the pivot needs to update to:
|
Row |
Start Count |
End Count |
|
Example |
34 |
34 |
|
Example 2 |
10 |
10 |
How would I achieve this?
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 here
On [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 😀
