topic Re: Count for max date in a month in App Development

Count for max date in a month

AlphaNik — Mon, 10 Feb 2025 11:07:11 GMT

Hi everyone,

I have a fact table with a unique id, a date and month name.

ex:

fact_id	fact_date	fact_monthName
1	21/01/2025	jan 25
2	31/01/2025	jan 25
3	07/02/2025	feb 25

I would like to write an expression to count my fact, but only for the last possible date of every month (based on current selection). in my example, if year 2025 would be selected, I should count id #2 for january and #3 for february.

I tried something like this:

=count( {<fact_date={"=aggr(max(fact_date), fact_monthName)"}>} distinct fact_id)

The expression is read, so it seems correct syntax wise, but the values are not what I expected. It seeems to count values for different dates.

Any help?

Thanks

Re: Count for max date in a month

JandreKillianRIC — Mon, 10 Feb 2025 12:21:33 GMT

Hi @AlphaNik

Try this

count( {<fact_date={"=fact_date=aggr(max(fact_date), fact_monthName)"}>} distinct fact_id)

Let me know

Regards Jandre

Re: Count for max date in a month

AlphaNik — Mon, 10 Feb 2025 15:08:51 GMT

Thank you, it worked.

Only thing, i had to create a temp table in order to order by date. For some reason it seems the value for aggr is evaluated only for the first row in load order (with monthName as a dimension). I tried to play with order by clause inside aggr to no avail.

Anyway it's working!

Regards Nik

Re: Count for max date in a month

JandreKillianRIC — Mon, 10 Feb 2025 15:24:14 GMT

Hi @AlphaNik

Great news! Im also struggling on an aggr sort. Should post something soon for other people to help me on this one!

Regards Jandre