I am trying to setup a measure that will always take active member for the latest date in the data granularity that is set for the table.
I have managed to get it working for weeks but not able to make it be dynamic so that it automatically changes based on the selected date granularity by the user. I have tried using first/last value but it was only able to select the max/min for the period not the absolute last value based on the date.
I put together a demo example for reference Summary Test.
How can I make it so that the value selected is always the latest value for the date granularity without creating additional controls or parameters.
For example when I select week granularity the value should be 107 for week starting Jan 18. When I select month granularity the value should be 106 for the month of Jan.
1 Like
Hello @firzen23, I want to make sure I understand the ask. In the example analysis, I am seeing a few different tables. How do you anticipate the user to change the date granularity? Is this going to be a parameter with a calculated field, or is it going to be based on the date selections they make in the filter?
Also, if it is month granularity for January, is you goal to show the number of active users for January 31st? If so, how do you want that to be displayed? Is this going to be used in a KPI, or are you wanting to use a table? If it is a table, what would you want to show in said table?
Sorry for all of the questions! I should be able to resolve this, I just need some more information. Thank you!
1 Like
Hi @firzen23,
Just checking back in since we haven’t heard from you in a bit. Were you able to see Dylan’s reply and/or find a solution yourself in the meantime? If you still have any additional questions related to your initial post, please feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.
Thank you!
Sorry for the delay I am no longer getting email notifications for this.
The idea being the user selects the “Date” column and selects Day, Week, Month, Year as the granularity.
The goal is regardless of the granularity selected by the user we always display the LATEST value of that period.
So for month it would be Jan 31 week would be the final day of week “Sunday”, year would be December 31st. Day would not change.
The plan is to display the data in a table similar to what is proposed in the example document
1 Like
Hello @firzen23, it is a little bit hard to test with this set of data because of the dates that are available in it, but this should at least send you in the right direction.
View in Arena: Selecting First/Last Date Value
Basically, I set it up so the user can select their date and the date granularity type. Then, to keep it simple, I use the Dynamic Date Filter calculated field to convert the date from the parameter to the last of a time period.
If it is a Week date, it will use truncDate to convert the parameter to the Week date, then addDateTime to convert it to the week following and subtract 1 day so it will equal the last day of the selected week. To make filtering easier, I returned 1 when the converted date parameter value equals the date from your dataset. Then, the filter on the visuals only has to check if that field is equal to 1.
Let me know if you have any questions!
1 Like
Thanks for investigating this I will have a play around in the Analysis of this.
I had something similar but it was causing poor user experience as some reports allow them to just click on the date and change granularity but this one required them to change a parameter which confused many of the users.
Do you know of a way to do this same calculation where that value is calculated by only changing the Date in the drop down of the column like so. Reference Video
If this is not currently supported by Quicksight are you able to create a feature request with the team as exposing the granularity selected by the date in calculated fields would be immensely useful for lots of reporting tasks I am working on.
Also as a final follow up question is there a way to lock changing the granularity of the column. If do go down the parameter path when members change the column granularity it will break the report so locking it would be ideal to reduce the number of support requests I get from them.
Thanks so much for your assistance on this matter