Hello, I have a calculation lastValue({verify_date}, [{login_date} DESC] , [{Unique Users}]) and I was able to use the last value calculation, my question now is how are you going to count those verify date?
verify_date login_date Unique Users
2-1-2024 12-1-2023 1
1-1-2024 12-1-2023 1
1-1-2024 12-1-2023 2
1-1-2024 12-1-2023 3
Answer whould be, Count = 3
Hello @danielreyes3rd, I am not sure I totally understand your question. You want to count the number of verify dates after running the lastValue calculation on them? I am not certain I understand from the example above how that answer would be 3 so if you could provide a little more information about your expected output, I will do my best to assist. Thank you!
Hi @DylanM , thanks for the response. I just really want to have a calculation to count on the data for a lastvalue calculation.
Hello @danielreyes3rd, I am curious, will there be multiple verify_date values that match the lastValue date? If not you could try a function that checks if the lastValue is equal to your date field and return 1 or 0. Then you could sum this field. The one thing to thing about though, is since lastValue is aggregated, you will need to aggregate your verify date to check them. My thought is we could turn them both to a string to avoid that error, so it would look something like this:
countVerifyDate = ifelse(toString({lastValueCalc}) = toString({verify_date}), 1, 0)
I am not certain the toString will solve the aggregation issue, so if it still returns an error let me know.
Alternatively, another thing you could try, would be to utilize distinctCountOver on the lastValue field. I am not certain these calculations work together, but it is worth a shot. Once you start applying aggregations to an aggregated field, it can be a bit complicated to find a solution that avoids any of the calculated field errors.
distinctCountOver({lastValueCalc}, [], PRE_AGG)
If that function throws an error due to PRE_AGG, you can also try removing it to see if the function accepts that. Let me know if either of these options help!
Hello @danielreyes3rd, did my last response help you resolve the issue you are facing in QuickSight? I will mark it as the solution for now, but let me know if you are still running into any errors while implementing the fix I suggested. Thank you!