Group by year and count distinct for a date of the year

[amaaiia]

Hi!

Given the following data as example:

year(date1), date1, value1

2022,2022-10-01,'a'

2022,2022-10-01,'b'

2022,2022-10-01,'c'

2022,2022-12-31,'b'

2023,2023-01-10,'a'

2023,2023-01-10,'b'

2023,2023-04-10,'a'

2023,2023-04-10,'b'

2023,2023-04-10,'c'

I have a measure1: count(distinct value1).

I want to create a bar chart with year(date1) as dimension and I want to apply measure1 to each year selecting the values (value1) of the max date1 of each year.

For example:

For year(date1)=2022, max(date1)=2022-12-31, so measure1=1 ('b')

For year(date1)=2023, max(date1)=2023-04-10, so measure1=3 ('a','b','c')

I would like to know how to edit measure1 so as to achieve this. I've tried with: count(date1={"$(=Date(Max(date1)))"}>} distinct value1), but Date(Max(date1)) is always the max value of all data, not for each year(date1).

Thanks.

[tresesco · Accepted solution]

Set analysis expression gets evaluated once for an object/chart. In your case, set analysis is not the solution (at least without going back to the script)

Try this exp:

count(distinct If(date1=Date(Aggr(NODISTINCT Max( date1),[year(date1)]),'YYYY-MM-DD'), value1))

[amaaiia]

I have created new dimension year(date1) AS year_date1 in load script, your function works changing [year(date1)] into year_date1.