Distinct count of Agent with comparing Date and Program

HarishBabu

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

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

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

 

 

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

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