Last date day for each month

[Nolgath]

(((floor(Count(distinct
{<Date={">=$(=date('2023-01-01'))<=$(=date(vCurrentMonthEndDate))"},duplicateStatus={0}>}
[_id])))
/
(Count(
distinct
{<
Date={">=$(=date('2023-01-01'))<=$(=date(vCurrentMonthEndDate))"},
country.sold={'$(=concat(distinct country,''','''))'},
[make.sold]={'$(=concat(distinct make,''','''))'},
[model.sold]={'$(=concat(distinct model,''','''))'},

[fuelType.sold]={'$(=concat(distinct fuelType,''','''))'},
[gearbox.sold]={'$(=concat(distinct gearbox,''','''))'}
>}
[_id.sold]))))
* day(MonthEnd(Date))

I have this calculation I need to do but where it says "* day(MonthEnd(Date))" should be the number of the last day of the month. I use this in a graph.

I can't seem to find a way to get the number only, i get null or just straight up 30 days instantly, for example today is 17th of april i need this month to be *17.

Any ideias?

[RafaelBarrios · Accepted solution]

Hi @Nolgath 

i think your problem its about how your data its in the back

first part of the formula looks good, but second part can give you more than one record for each row so it should be returning null value for you,  and that is becasue both are in different levels of aggregation.

so, you can try this

* max(day(MonthEnd(Date)))

or create a new field in the script from "day(MonthEnd(Date)))" but still can give you wierd cases.

hope this help,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 

[Nolgath]

That worked just as expected! Thank you!