topic Aggr() function with month for distinct count over the months in App Development

Aggr() function with month for distinct count over the months

Stanislav1 — Fri, 15 Nov 2024 22:23:08 GMT

Hello there!

I am struggling for a while with this one and I need your help. I want to see the distinct count per month for Campaign ID in a line chart using aggr function.

I have this so far: Max(Aggr(Count( {<$(v_SA_Calendar_Dynamic)>} distinct[Campaign Id]),[Date Start]))

The above number (31) is the total campaigns for 2021 and on the bottom you can see the line chart where for some reason the total count is 36 not 31.

Re: Aggr() function with month for distinct count over the months

rubenmarin — Wed, 09 Nov 2022 08:16:46 GMT

Hi, that could be because some of the campaigns will count for more than one month, this could happen if the month field used in chart is not directly associated with the Date start, maybe it comes from the sales month or other field where one campaign could be in more than one month.

In this case, maybe:

Count({<$(v_SA_Calendar_Dynamic)>} distinct Aggr(If(Month=Month([Date Start]), [Campaign Id]),Month,[Campaign Id]))

Re: Aggr() function with month for distinct count over the months

Stanislav1 — Wed, 09 Nov 2022 09:26:49 GMT

Hi, you are completely correct. Some of the campaigns are in more than one month. I tried your solution:

Count({<$(v_SA_Calendar_Dynamic)>} distinct Aggr(If(Month=Month([Date Start]), [Campaign Id]),Month,[Campaign Id]))

Still no success here.

Re: Aggr() function with month for distinct count over the months

rubenmarin — Wed, 09 Nov 2022 10:18:33 GMT

You can add a table with Month, campaign id and start date and see the relation between these fields, I suppose each campaign id would have only one start date.

Maybe is just: Count(Distinct If(Month=Month([Date Start]), [Campaign Id])

Re: Aggr() function with month for distinct count over the months

Stanislav1 — Thu, 10 Nov 2022 07:51:25 GMT

The issue is resolved thank you