How to create a lineitem-dynamic graph and chart basing on a driver lineitem/list

PopCC

Hi, everyone,

 

I have an requirement from client that users select a standard/method (may be list types) from a drop-down in a lineitem (list formatted), and the graph/charts will automatically change the data items accordingly. Let me put it in more details with some examples.

 

Driver (parameters) Image:

Country:	Indonesia
Analyis Method:	Revenue Factors
Referral Actual:	FY16
Starting Actual:	FY17
Latest forcast:	FY18

Waterfall Image: As attached.

 

If users select "Revenue Factors" for the Analyis Method, then the waterfall graph will display data items (probably set as not list, but lineitem, since each requires a different calculation logic)  such as quantities, discounts, exchange rates, etc. which contributes to the gap between FY17 and FY18, but if users change the analyis method to "Marketing Segments",  the waterfall graph will display the gap of data items like Product Segment A sales,  Product Segment B sales, Services sales, and etc.

 

And another complicated thing is that, for some users, they don't want the result basing on all the factors/segments we prepared, instead they just want graph to be displayed in 3 or 4 main factors/segments, and group all the other data items as "the others", for some users, they may want more detailed analysis on all the factors/segments or 5 or 6 main ones and all the others grouped as "the others" in a flexible way.

 

How can I achieve that?

For the first one, I think that I may set the all the line items as a line item subset, and put it in another module (filter module) and set its boolean value according to the dropdown item (driver module) selected by users, and then set a filter for the waterfall graph (final module) basing on boolean the filter module. But the problem is that users have to manually refresh the dashboard everytime the change the dropdown value. Any better idea especially one which doesn't require manual refresh on filter??

 

For the second one, I have not much clue on how to do it, maybe creat another lineitem as "the others" and also put it in the same lineitem subset as mentioned above, while the value is based on users' selection for main factors/segments (boolean) in another module which is displayed on the dashboard. And as for filtering the factors/segments including the "the others", may do it in the same way as above.

 

Any discussion or idea is welcome. Thanks in advance!!

Surya.Arif

Hi,

This is an interesting case that you have,
But before we jump to how i would suggest the approach,
I would first point out some of the factors that you should consider before doing this approach.
I had this case before and after I completed it then I realize what was my mistake.
Hope you do not repeat my mistake.

 

The Pros
- You will have a cool looking dashboard that dynamic and sophisticated.
- you can show the "Revenue Factors graph", "Marketing Segments", maybe some
"Expense graph", "Sales Commision" and all that in one graph just by changing the parameter.
- Each person can have their own clasification, some will have 3 segment, other will have 4 segment
it easy for them to change as they like

The Cons
- The facts remain that you ONLY have one graph. Because it is depend on the parameter.
- When the management wanted to compare how good is the "Revenue Factor" performance compare to the
"Expense", They can't. Because you can only show one graph at a time.
- The second option (custom segment) actually is the worst in my opinion, because when you make this
possible for them. You actually make them talking in each other with the different language.
for ex. if John have 3 item for category "Other", and George have 5 item for category "Other".
imagine if you are the CFO and wanted to know your revenue segment.
Of course based on the segment, George will have a better revenue for "Other" because it is 5 item
compare to John only 3 items.

Those are my thought on your case, hope you will take that into consideration.

 

Okay on to the next section.

In your case it is better if you do the second one first (custom segment) then go into the (custom Report)

 

1. You make a dynamic "segment filter" module for each user so they can change their
preference by them selves

For Ex.
you will have list maybe as follow :
- list of products
- list of users (default by system login)
- list of Segments

Then you make the "Segment filter" module
with "Product" and "users" as the dimension.
and 1 line item with the "list of segments" as the data type.

in this module user can change their product will have what kind of segment as they like
this will be the "Segment filter" module for your summary operation.

2. you have to make a calculation module that will take data from your "sales module" and connect it
with the "Segment filter" module so you can sum the data base on your preferred segment.

After doing this 2 step your second case should be covered.

3. For your first case you should have a new list:
- List of graph option
this list will contain all the different scenario that you like.

 

4. you will have a "graph parameter" module
that contain
user as dimension
and line item of "graph of user choice" with the "list of graph option" as the data type.
you can also add all other parameters as line item as you like that will be used in the calculation
like you mention before "referral actual", "Starting actual" ......

 

5. Then you take the data from the previously created "second case module" into one big calculation module
using the parameter taken from the "Graph parameter" module
Why i call one big calculation module, because essentialy you will have to have one final "Line Item"
for each item in "List of graph option"
for ex.

Line Item 1 = "Revenue Factor Amount"
Line Item 2 = "Marketing Segments Amount"
Line Item 3 = "Expense Amount"
Line item 4 .... and so on

 

6. you will have to have one line item to take the "graph of user choice" value from the "graph parameter" module
Line item 99 = graph of user choice. Formula = graph parameter.'graph of user choice'

 

7. The last line item will be the
Line item 100 = "Graph Amount" with the nested if formula
Formula :
If line item 99 = "Revenue Factor Amount" then "line item 1".value
elseif line item 99 = "Marketing Segments" then "line item 2".value
elseif line item 99 = "Expense Amount" then "line item 3".value

this way the "Line item 100" will reflect the amount that the user choose.

 

8. You make the graph based on the line item 100.value.

 

Hope this help.

 

Regards,

 

SA

 

 

harish_bk

Hi PopCC
You can acheive this by following below process:
1) Convert the lineitems to lineitem subset.
2) Create new module with the lineitem subset and other lists present in original module

3) Use collect function to get the data or you can use other functions as required

4) Create graph on the new module

Regards
Harish B K