Distinct count of Agent with comparing Date and Program

I have Module in DataHub without any Dimension
I need to create a column that count distinct (Unique) Value of Agent
Program and Agent are in Text Format

Date	Program	Agent	Distinct Agent count
01-01-22	1	21	3
01-01-22	1	22
01-01-22	1	23
01-01-22	2	21	3
01-01-22	2	22
01-01-22	2	27
01-01-22	3	21	4
01-01-22	3	22
01-01-22	3	35
01-01-22	3	46
02-01-22	1	21	2
02-01-22	1	24
02-01-22	2	29	1
02-01-22	2	29
02-01-22	3	52	1

Accepted answers

ManjunathKN

@HarishBabu

you have asked for distinct count and not count.

if agent are repeated it should give 1 count for each agent.

if you want agent count then use this rank logic.

RANK(Program, ASCENDING, MAXIMUM, TRUE, Group By)

Thanks,
Manjuanth

All comments

ManjunathKN

Hi @HarishBabu,

How you can have so many rows without any dimensions?

I think it might have Day as dimension.

Thanks,

Manjunath

ShubhamCh

Hi @ManjunathKN

I think this is a transactional module over a transactional list which has IDs. Usually Data hubs have transactional modules. I believe @HarishBabu has ignored that left most column which shows the IDs. That's my understanding of the problem. My assumptions might be wrong.

Thanks!

-Shubham

ManjunathKN

Hi @ShubhamCh

yes, might be. However, this can be solved by rank and isfirstoccurance.

Thanks,
Manjunath

HarishBabu

There was a list created with Concat(Date,Program,agent), which is not good practice to have But following that method because it was already built in my Project.

HarishBabu

Thanks for your replies.

Hi @ManjunathKN ,
Rank function is for Number, Program and Agent are in Text Format

Isfirstoccurrence require list

Requirement - Filter Date, Filter Program, Count(Distinct(Agent))

ManjunathKN

Hi @HarishBabu

you need to use your concat list for this, without list you cannot have different rows in anaplan if your line item is in columns and vice versa. if you have different format use the data type conversion ex: Value() function.

Let me know if below solution works for you.

Blueprint View

Logics

IF Isfirstoccurance THEN RANK(Agent, DESCENDING, SEQUENTIAL, TRUE, Group By) ELSE 0
TEXT(Program) & "|" & TEXT(MONTH(Date)) & "/" & TEXT(DAY(Date)) & "/" & TEXT(YEAR(Date))
ISFIRSTOCCURRENCE(Group By, 'Test List L1')

I prefer to do this in spoke model.

Thanks,
Manjunath

ManjunathKN

Hi @rob_marshall,

Need your advise,

Where do we have to do this, is it okay to do this datahub or in spoke by brining the transactional list. Since it has few text functions

Thanks,
Manjunath

HarishBabu

First of all Big Thanks for your efforts @ManjunathKN
As per this formula i expect the count of Distinct Agent which is 25 but here it's showing 15
Not able to figure out why?

Thanks,

HarishBabu

Screenshot_(182)_151235.png

ManjunathKN

@HarishBabu

that difference 10 is agent columns not assigned i believe. Which means that only 15 agents might have provided and in other 10 cells agents are not assigned or blank.

Thanks,
Manjunath

ManjunathKN

@HarishBabu do you have alphabetical character in your agent ID?.

HarishBabu

No
Only Number because Source of Agent ID is text so maintaining here as Text
No Alphabetical characters

ManjunathKN

@HarishBabu

you have asked for distinct count and not count.

if agent are repeated it should give 1 count for each agent.

if you want agent count then use this rank logic.

RANK(Program, ASCENDING, MAXIMUM, TRUE, Group By)

Thanks,
Manjuanth

HarishBabu

Yes, Requirement is Distinct Count only. But all 25 Agents are different.

I tried with Maximin also still showing same.

Thanks,
HarishBabu

ManjunathKN

I could see some are same here.

cannot figure out since, it is hidden.

Thanks,
Manjunath

HarishBabu

Hi @ManjunathKN ,
Little confusion because of sorting. After ranking by Program the value is matching.

Thanks a lot.

Thanks,

HarishBabu

Quick Links

Can you help?

Unanswered questions

Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…
Anaplan Report page issue in the UX
Hello community, I have an issue with anaplan report (functionality). The issue I have is within couple of slides. The data in that report is scrolling into a new page. There is a hierarchy in the rows and line items and time in the columns. My problem is, the hierarchy is getting broken in the second page. One of the node…
ADO - S3 incremental load vs. full replace
I want to create an S3 connection that will have monthly data stored in individual csv files that is connected to ADO. Around month end, data will be uploaded multiple times, replacing existing csvs. When this happens I want to replace the existing data for that month with the new file. I don't want to have to create a…