How can we aggregate all values before a certain month?

Question & Answer
, ,
1

Hi all,
I intend to generate values based on date time filter (or custom parameter) before a certain month.
For example: if I click July 2023 on the filter (filter must be year-month basis), the value on a dashboard should show an aggregated value from Jan to June
and values I used are created with calculation field with some ifelse condition
How do I achieve this?

2

Hi @Tun-Yang_Chou !
Welcome to the QuickSight Community! You can use a relative date filter and a parameter to achieve those results.

Best Regards,
Kellie

3

Hey,
Thank you for replying, but I would like to know how to combine these 2 feature and achieve it?

4

Hi @Tun-Yang_Chou,

What’s the aggregate value you’re trying to calculate? First create a parameter for your date and use it in your control. Then you can create a calculated field like this for your visual. It has a condition based on the value of the parameter that you select in the control.
image

This is just an example, so your calculated field will vary depending on what exactly you’re trying to calculate.

5

Hi David,
I am just trying to count how many things we need to do (with some if else condition) by the end of May (it should show sum of numbers from 2023-01 to 2023-05/31 when I click the parameter on 2023-06).
Can this kind datetime parameter set to month level instead of day (date), like 2023-05 instead of 2023-05-03?

6

Hi @Tun-Yang_Chou,

I don’t see a way to change the granularity of the datetime parameter to month.
image

I think what you may have to do is as workaround is create 2 calculated fields to extract the year and month as integers.
Order Year = extract(“YYYY”, {Order Date})
Order Month = extract(“MM”, {Order Date})

Then create 2 integer parameters for the year and month and set the condition in your ifelse like this:
{Order Year} <= ${OrderYear} AND {Order Month} < ${OrderMonth}

Add those 2 parameters as controls and use them to select the year and month separately.