How to get a count in Anaplan?

gramasub · 2020-07-21T20:49:44+00:00

OPI #Mgr 1. OPI #Mgr2. OPI #Mgr3. OPI #Mgr4. OPI…

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?

Accepted answers

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

All comments

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.

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

gramasub

Thanks Harsha, It works 👍

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.…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…