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

Excel date format for February 2030, 2031 and so forth don't align with Anaplan format
Hello, I’m running into a recurring issue when importing CSV files into Anaplan, specifically with dates in February for the years 2028, 2029, and beyond. Here’s what’s happening: In Excel, the date format is set to Custom (e.g., 2/26/2025). When imported into Anaplan, the date is interpreted as 26-Feb, where: Month = Feb…
RANKCUMULATE not working when adding additional dimensions to module
I have an issue where I am getting the expected result from RANKCUMULATE when using one dimension, but when I add an additional scenario dimension to the module, it doesn't calculate the way I'd expect. Here are some screenshots to explain the case: Module 1 - calculates cumulative total for list items in list "Age"…
Introducing Anaplan API Python SDK
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…