Hi there, I’m trying to calculate the last 3,6,12 months distinct count of customers and it should be month wise (including the current month).
For eg L3M : if we take June month it should calculate April ,May ,June like wise for all the months
1 Like
Hello @Surendar, welcome to the QuickSight community!
We can definitely manae this expectation. Basically, we would want to create 3 calculated fields to return values for dates in those time ranges. With a combination of extract, addDateTime, and now(), we can build out those calculated fields.
I’ll write out an example for the last 3 months calculation. We will want to nest addDateTime functions so we can compare the start of the last 3 months dynamically based on the current date:
ifelse(addDateTime(1-extract('DD', now()), 'DD', addDateTime(-2, 'MM', now())) <= {date field} AND now() >= {date field}, {value}, NULL)
Since you are using this in a pivot table, you should not need to aggregate the value in the calculated field. The pivot table contains partitions so it should do the work for you. Let me know if you have any further questions!
Hello @DylanM .
Thanks for your reply. Actually we need distinct Count of Customers for Last 3M .
The given calculated working fine for the value and not for distinct count.
ifelse(addDateTime(1-extract(‘DD’, now()), ‘DD’, addDateTime(-2, ‘MM’, now())) <= Date AND now() >= Date, distinct_count({CustomerCode_Order}), NULL)
Error : Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.
1 Like
Hello @Surendar, if you return the field you want to aggregate and then set the aggregation in the field well to count distinct, that should resolve this issue.
ifelse(addDateTime(1-extract(‘DD’, now()), ‘DD’, addDateTime(-2, ‘MM’, now())) <= Date AND now() >= Date, {CustomerCode_Order}, NULL)
Hello @DylanM
The below table has the right values.
| Month | UPC L3M | UPC L6M | UPC L12M |
|---|---|---|---|
| 2024-02 | 0 | 0 | 0 |
| 2024-03 | 225 | 225 | 225 |
| 2024-04 | 1101 | 1101 | 1101 |
| 2024-05 | 6399 | 6399 | 6399 |
| 2024-06 | 16184 | 16234 | 16234 |
As per your calculated field the values are not correct.
we need to add distinct count within calculated field.
Hello @Surendar, can you recreate this pivot table and any of your calculated fields in a QuickSight Arena analysis so I can test out a solution? In order to share it with me, publish it to a dashboard, click the share icon in the navbar, make it public, and post the link in your reply. That will make it easier for me to debug this issue and help you find a solution.
To me, the problem is likely being caused by how these values are being added into the pivot table. I may be able to find a work-around if I can edit in the QuickSight Arena analysis. Thank you!
In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena