CODEWITHSUNDEEP
amazon-quicksight
Carlos Walker
Carlos Walker

Reputation: 1

Quicksight: Aggregation Over Last Day of Month

I am working on a chart bar that the x-axis is a date aggregated by month. ¿Is is posible to aggregate by day but only show the last day of each month using a filter o by other way? Other possibility i see is to create a calculated field to show the value for the last day of the aggregated period but i have not been able to do it ¿Is it posible?

Upvotes: 0

Views: 6064

Answers (3)

Carlos Walker
Carlos Walker

Reputation: 1

i finally solve it like this using the posted formulas:

parseDate
(
concat
(
toString(extract("YYYY",addDateTime(1,"DD",addDateTime(1,"MM",truncDate("MM",date)))))
,"-"
,toString(extract("MM",addDateTime(1,"DD",addDateTime(1,"MM",truncDate("MM",date)))))
,"-"
,toString(extract("DD",addDateTime(-1,"DD",addDateTime(1,"MM",truncDate("MM",date)))))
)
,'yyyy-MM-dd')

And because current month have not ended so i don't have data for the the last day i use an ifelse like this:

ifelse(
parseDate(
concat(
toString(extract("YYYY",addDateTime(-1,"DD",addDateTime(1,"MM",truncDate("MM",date)))))
,"-"
,toString(extract("MM",addDateTime(-1,"DD",addDateTime(1,"MM",truncDate("MM",date)))))
,"-"
,toString(extract("DD",addDateTime(-1,"DD",addDateTime(1,"MM",truncDate("MM",date))))))
,'yyyy-MM-dd') 
>= now()
,addDateTime(-1,"DD",now())
,parseDate(concat(toString(extract("YYYY",addDateTime(-1,"DD",addDateTime(1,"MM",truncDate("MM",date)))))
,"-"
,toString(extract("MM",addDateTime(-1,"DD",addDateTime(1,"MM",truncDate("MM",date)))))
,"-"
,toString(extract("DD",addDateTime(-1,"DD",addDateTime(1,"MM",truncDate("MM",date)))))
)
,'yyyy-MM-dd')
)

And finally i created de following calculated field:

sumIf(base,formatDate(date,'yyyy-MM-dd') = formatDate({fecha_last_day},'yyyy-MM-dd'))

The result:

The value related to the last day for each month, and for current month the last day with able data

Upvotes: 0

GeekyElephant
GeekyElephant

Reputation: 11

calculated field to show the value for the last day of the aggregated period but i have not been able to do it. Is it posible?

Using the previous answer, I used this formula in Quicksight calculated field to get the figure for 'last day of the current month'. You can change now() to get the last day of another period.

extract("DD",addDateTime(-1,"DD",addDateTime(1,"MM",truncDate("MM",now()))))

Upvotes: 1

Tatyana Yakushev
Tatyana Yakushev

Reputation: 71

I'm not exactly sure what you are trying to do to provide the best guidance.

If I had to write an expression to calculate last day of the month, I would

  1. Use truncDate function to extract month and year information from the date
  2. Add 1 to the month, unless it is December
  3. Get first day of the month (which always has 1 as the day)
  4. Use addDateTime to subtract one day

For example, given a date 02/19/20 (in the format MM/DD/YYYY). 02 is the month number 20 is the year number The following month is 03 Beginning of that month is 03/01/20 Last day of the previous month will be 02/29/20 which I will get by subtracting a day from March 1st.

Upvotes: 0

Related Questions