Max day for that month

Question & Answer
,
1

Hello Quicksight Commuity,
If someone can help me with a expression to get the max day for the month. I want to set a flag =1 if the day is max day for that month. For example:

20230401 0
20230430 1
20230102 0
20230131 1
I have the date in format yyyymmdd from the database. I am trying to add a calculated field which will tell me it is the max day of the month.

Thanks,
Ro

2

@kochar -
You can calculate max date of a field using the maxOver function -
maxOver({your date field}, [ ], PRE_AGG).

Here’s a link to an article with more details on usage.

3

I want to set the flag =1 for last day or max date for that month period.

5

attempting to use this function to retrieve the last value of the {data_date} field within each month, sorted by ascending dates but still errors. No idea what the error is

lastValue(
[{data_date}],
[{data_date} DESC],
[
truncDate(‘MM’, {data_date})
]
)

6

So from where I’m sitting you need to do a few things:

  • Convert the yyyymmdd value into a date Quicksight can understand
  • Determine the last day of that month
  • Compare the last day of that month with the date in question to see if they match

You can choose to do this all in one go, or break it up into multiple calculated fields. The choice is yours. I’ve done it in steps to make it easier to follow along.

To start, we need to use parseDate to get you date values correctly formatted. Edit: also realized that your current date column is likey being stored as an int, so you’ll need to convert the int to a string first before using parseDate. You could create a field called formattedDate with the formula parseDate(toString({yourDateColumn}), 'yyyyMMdd')

Then using that formatted date we want to use the month to determine when the last day of the relevant month would be, with a calculated field called lastDay and formula addDateTime(-1, 'DD', addDateTime(1, 'MM', truncDate('MM', {formattedDate})))

Then finally you want to compare the last day of the month with the dates in your dataset, so you could do a few different things, but here’s one example for a calculated field called isLastDay.

ifelse({yourDateColumn} = {lastDay,} 1,0)

You could combine all of these into a single formula, but this helps you see it step by step and also verify that the above is doing what you think it should be.