How to Count Text Value based on the Input.
Hi All,
I'm fairly new to Anaplan. I want to count a list item (Model State in Text format) to be able to output its count to my dashboard. The three current inputs are Maintenance, Production, and Unlocked. I want to be able to show on my dashboard how many models are per state. I tried fiddling with the IF function for booleans but to no avail. Any suggestions would be nice. Again, I'm new to Anaplan so please go easy on me. Thanks in advance!
Best Answer
-
We should try avoid two things here:
1. The use of SELECT - Hard coding is not good practice and hinder flexibility and maintenance
2. The use of Text - Text is the least efficient construct in Anaplan
Anaplan works best with List formats, so:
1. Create a list for your Model State and include the three elements, Maintenance, Production, and Unlocked
2. In your Data module, change the format of the Text field to the Model state; you may need to input/import the data again.
3. In a module purely dimensioned by Model (you should have a system module for Model details), add a single line item with the formula 1. This may be the same module as 2. I have assumed the data module is different here
Now the logic can reference the model state using SUM
4. Create a target module dimensioned by Model State (from 1) and create a line item to hold the summary count, the formula would be something like:
Model details.count[SUM:Model data.model State]
I hope that helps
See the following article for more guidance of Module design
Best practice for Module design
David
3
Answers
-
Hi
From your question , I assume that you have a list of models and against each model you want to input it's state in text format. Now to answer your question, create three new line items with formula like First Line Item: "IF Model State = "Deployed" THEN 1 ELSE 0" , Second Line ITem: "IF Model State = "Maintenance" THEN 1 ELSE 0 . Now to find the count , you can use these line items with a SELECT statement on Model list top level.
If I understood your question right, and you have difficulty understanding the solution, please share screenshots.
Hope that helps
1 -
I was able to apply both of your answers. I created a list named model states that houses the different states. I changed the data format of the model state property from text to list with reference to the model states list. I then proceeded creating a module that references the model state property and created 3 line items for the different states then applied the corresponding formula which looks like this:
IF Current Model State = Model State.[Specific Model State] THEN 1 ELSE 0
It works! Thanks to the both of you for your help. Just one quick question. What would be the effect if I tag the model state list I made as a non-production list?
0 -
I would still avoid direct references to the model State - That is, in effect, the same as the SELECT.
If you are using ALM, you will not be able to set Mode state as a production list because you cannot have direct references on production lists.
If you do need Model State to be a production list, you will need to use the SUM or LOOKUP construct
If you set Model State as Non Production, any changes to that list will need to be made in DEV and synchronised
I hope that helps
David
1 -
Hi David,
Thank you for the tip with the direct references. I'm now trying to reference using lookup so I can tag the Model State list into a production list but I'm having a hard time figuring out the correct formula.
What I did is I referenced the model state line item of my module from the Current Model State property of my list (Based on the Model State list that I created as well). I then made three line items in my module (one for each model status [Maintenance, Production, Unlocked]). My problem is I can't figure out the lookup formula that I should put into these 3 line items to avoid direct references.
0 -
Ideally, you should get the solution as in the below example.
Below example is with 3 different states - A,B,C and with 6 models M1-M6
1. Create an input module where you have model state against your model (Dimension of the module) and a line item with 1 as a number. So the dimension is model and there are 2 line items - Model state and count
2. Your target module with model state as a dimension and count as a line item with the formula as written
Model State Count.Count[SUM: Model State Count.Model State]
The way you are trying to achieve this,
1. You will have to create a module with no dimensions, n(Number of model states) line items with format as model state and values of model state list (A,B,C in my case) in respective line items as below
2. To get the result in 3 different line items now (Count of states A,B,C in my case), you create a module (No Dimension) with 3 different line items and put the following formula pointing to 3 different line items above (Point 1)
Model State Count.Count[SUM: Model State Count.Model State, LOOKUP: 'Lookup1'.Model State A]
---- Not able to attach the screenshots. Happy to mail it to you ----
Hope this helps
Thanks
0
