Feature Request - Conditional periodToDateSumOverTime

Question & Answer
, , , , , ,
1

I’m trying to create a calculated field that shows the period to date sum over time but with:

  1. summing a field based on a condition
  2. using a more specific period (e.g. instead of WEEK or MONTH, I need 90 days)

For part 1, I’ll give an example:
field A is fruits, field B is quantity of fruits

I’d want for each day, the number of unique fruit types (apple, orange, etc) over the last 90 days where the total quantity of that fruit is at least 5 over the 90 day period.

Is this possible in Quicksight? I’ve tried with several functions but always encounter “please contact the Quicksight team” when I try to visualize it.

2

Hello @oconnellpatrickc , welcome to the Quicksight community!

You should be able to achieve this using a conditional statement similar to this:

Could you share the calculated fields that have thrown that error?

1 Like
3

Hi @oconnellpatrickc ,

Ensure that you have the ‘fruits’ field added to the visual. Add a filter to the visual to limit data to 90 day period (absolute range or relative filter as your use case requires). Then, add another filter on ‘quantity of fruits’ field for the visual, set the Aggregation to sum (in the filter), Filter condition to Greater than or equal to and the value to your desired cut off.

Above is easiest way to visualize it. If you still have the need to create a calculated field (perhaps for use in some other logic), create a sample analysis in Arena and we can take another look at it. Marking this as solution for now.

Regards,
Arun Santhosh
Pr QuickSight SA

1 Like
4

Hi Arun,

The problem is that I don’t want to filter to quantity of fruit at least 5 for a particular row, I want fruits where the quantity of that fruit is at least 5 over the whole period.

So for each day, we would look back 90 days and see how many fruits have a quantity of at least 5 over that 90 day period, and mark that (e.g. 2 fruits for that day).

I don’t want to limit the overall visual limit to 90 days because I may want this visual over a period of 6 months for example, where each day shows the cumulative number of fruits that had at least 5 in quantity in the trailing 90 day period.

5

Bump @ArunSanthosh @duncan

6

Hello @oconnellpatrickc and @ArunSanthosh !

Can you share a few of the calculated fields that are throwing “please contact the Quicksight team” when you try to visualize them?

Even if you don’t want to use filters @ArunSanthosh’s method about making sure that all the necessary field are in the visual is correct.

7

periodToDateDistinctCountOverTime(
distinct_countIf(
fruit,
fruit_quantity > 5
),
date,
MONTH
)

I get “Please contact the Quicksight team to solve this issue” when using this field as the value and date as the trend group.

It’s not that I don’t want to use filters, it’s that this filtering suggestion doesn’t accomplish what I’m looking for. I need to know for each day over a period, what the number of fruits are on that day that have a quantity of 5+ over the last 90 days. Filtering the whole visual to 90 days means I only get one day of this cumulative value.

Similarly, filtering “quantity of fruits” to >5 would only display fruits where the fruit quantity is >5 that day. This is not what I need. I need fruits where the fruit quantity is >5 over the last 90 days.

8

Thank you for letting us know. I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!