Highlighted
New Contributor

Need Help on Filtering Data

I have a module containing multiple line items. One of the line item is "org code" with format "Text". I need to filter data based on the values in this line item. "Org code" is actually representing organizations under a department. One department can have multiple org codes. As an example, I am attaching a screen print showing some of the line items of this module.

 

For the sake of understanding, let's say there are 2 type of org codes -

a.) Normal org codes - Having just the numbers in them e.g. 664401010.

b.) Input org codes - They end with "_Input". e.g.  H1_Input

 

While selecting any department, I should be able to see org codes under it. There are 3 possible scenarios which I have to handle.

 

Scenario1-

Here I have taken some sample values of Org codes.

664401010
660401010
680436001
615401002

In this case, all the values should appear.

 

Scenario2-

 

In this case, I have taken following 5 sample values of "org code".

 

664401010
660401010
680436001
615401002
N1_Input

In this case, I want filters to show all the values except for "N1_Input".

 

Scenario3-

 

In this case, I have taken following 4 sample values of "org code".

 

N1_Input
N2_Input
N3_Input
H1_Input

In this case, all of the above values should appear.

 

Basically, the ask here is whenever there are normal org codes then show all of them and whenever we have both i.e. normal as well as Input org codes then only show normal org codes and when we just have Input org codes

then we should show all the Input org codes.

 

Org Codes.JPG

5 REPLIES 5
Highlighted
Master Anaplanner/Community Boss

Re: Need Help on Filtering Data

Hi @Kamal 

 

Thanks for providing all the scenarios this is great!

If all the scenarios need to be filtered separately you could create a boolean formatted line item for each scenario.

Then define a rule which determines whether that code should be included in that scenario filter. 

For example for scenario 1

You would need to need to count the characters using the formula - https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/LENGTH.html and then say if it greater than or equal to 9 then true.

For scenario 2

you could use the find formula to find the term 'Input' 

https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/FIND.html?_ga=2.124245455.473078... then if it finds it determine it as a False.

 

For scenario 3 same as 2 but True.

 

Then you would filter on the particular scenario line item. 

 

I hope this helps! 

 

If you need any further explanation please let me know.

 

Thanks,

 

Usman

 

Highlighted
New Contributor

Re: Need Help on Filtering Data

Hi Kamal,
Usman has given a good solution , but just to give a alternative for 1st part where you want to filter out number only.
You can create a new line item with number as format.
in that line item , Put formulas as :
Value(org code) , so wherever there are number in org code , it will get displayed in new line item and text will come as "NaN"
Now you can reference it in the last boolean filter where you want to filter out the respective data.

Hope it helps!!
Ronak


Highlighted
Master Anaplanner/Community Boss

Re: Need Help on Filtering Data

Hi Kamal,

 

Try this,

1.png2.png

Apply the filter based on FILTER? Line item.

I hope I answered your question.

 

Thanks,

Vignesh M

Highlighted
Master Anaplanner/Community Boss

Re: Need Help on Filtering Data

HI @Kamal,

Interesting post!! Kudos for bringing this in Community.

Here is my approach to achieve this solution.

Step 1: Create 3 line items as below shot.

Need Help on Filtering Data 1.PNG

 

 

Step 2: You can follow the formula based on the above SS. Note: For Dep selection I am using different Module.

Need Help on Filtering Data 2.PNG

 

Scenarios check,

Without filter

Need Help on Filtering Data 3.PNG

 

After selecting D5,

Need Help on Filtering Data 4.PNG

 

After selecting D1,

Need Help on Filtering Data 5.PNG

 

After selecting D3,

Need Help on Filtering Data 6.PNG

 

I hope you would get some clear idea how to work this out. You can merge the formula and reduce the line item but as Experts says we go with our Planual way!!! 🙂

Thanks and Regards,

Kavin.

Regards,
Kavin.
Highlighted
Community Boss

Re: Need Help on Filtering Data

Hi Kavin,

 

I liked the way you have provided the solution to the member. I think it can be done multiple ways but your solution can be applied in most of the cases.

 

Kudos!!