How to get a count in Anaplan?

gramasub

OPI #	Mgr 1. OPI #	Mgr2. OPI #	Mgr3. OPI #	Mgr4. OPI #	Count
8116	2933	8143	32521	8116	1
90148	2933	8143	32521	90148	1
99527	2933	8143	32521	99527	1
374170	2933	8143	32521	374170	1
376060	2933	8143	32521	376060	1
387556	2933	8143	32521	387556	1
32521	2933	8143	32521		7
91446	2933	8143	389387	91446	1
141746	2933	8143	389387	141746	1
275678	2933	8143	389387	275678	1
338122	2933	8143	389387	338122	1
351413	2933	8143	389387	351413	1
375917	2933	8143	389387	375917	1
382607	2933	8143	389387	382607	1
391712	2933	8143	389387	391712	1
391756	2933	8143	389387	391756	1
389387	2933	8143	389387		10
8143	2933	8143			18

 

in the above flat file, i want to get a count for first column data. for example in the first column, how many times "8116" present in the sheet ? count from column 2nd column till Mgr4 column. in this example "8116" present only once but "32521" present 7 times. I can achieve this in excel. How can i achieve this in Anaplan? Can any one please help me?

Harsha

Hi Ganesh,

 

1. Create a list which has all the OPIs.

2. Use FINDITEM for each column to find whether that OPI is present in that column or not.

  FI_1= FINDITEM( OPIs, Mgr 1. OPI # ) , FI_2 = FINDITEM( OPIs, Mgr 2. OPI # )....

3. Create another line item, say Count and write 1.

4. create another line item and Sum count based on finditem.  -> Count[SUM: FI_1] + Count[SUM: FI_2]...

 

this should work. let me know.

 

Thanks,

Harsha

 

Misbah

@gramasub 

 

See if this helps

 

First create a list of OPIs in your model and and then use that list as a dimension in your module. Create other line items as OPI list formatted line items. Use SUM & LOOKUP in your Result Line items to get the desired count on any of these columns.

Note: Don't use SUM & LOOKUP together as there are performance issues.

 

 

 

gramasub

Thanks Harsha, It works 👍