Despite the COVID-19 outbreak, our team continues operating at full speed. We are always here to support and answer all your questions.

Feel free to reach out by filling this quick form.

Fill the form
Get Free Trial

Same dimensions, different measures based on a “data_type” column in the data

Answered
Jason Cox asked on March 9, 2020

I think I’m understanding how we can illustrate different aspects of our business using flexmonster. 
The idea is that flex only accepts a flattened 2d sheet of data. I’ve defined my Dimensions(all strings) and my metrics (all int/float/double) for each type of record.
Dimensions across all data (All rows will populate these): organization_name, company_name, salesrep_name, client_name, fiscal_year, fiscal_month_number, product_channel, product_group, product, industry, record_type (ENUM: booked, goal, pipeline, etc..)
Dimensions for booked rows (all data has these columns, just blank when record_type is not booked): order_id, app_order_status
Dimensions for goal rows (all data has these columns, just blank when record_type is not booked): opportunity_id (string salesforce opportunity id)
Dimensions for pipeline rows (all data hast hese columns, just blank when record_type is not booked): pipeline_gross, pipeline_net
 
Metrics for record_type=booked (All other metrics have 0s):gross, net, vendor_spend
Metrics for record_type=goal (All other metrics have 0s):goal_target

  • Note: one row per fiscal_month per product_channel  per salesrep), likelyhood_close_percent

Metrics for record_type=pipeline (All other metrics have 0s): pipeline_gross, pipeline_net
Example of data: (One of each record type)
record_type, organization_name, company_name, salesrep_name, client_name, fiscal_year, fiscal_month_number, product_channel, product_group, product, industry, sf_opportunity_id, dti_order_id, revenue, revenue_net, vendor_spend, pipeline_gross, pipeline_net, goal_target
‘booked’, ‘Organization Name1’, ‘Booking Company Name1’, ‘Salesrep Name1’, ‘Arizona Company Name’, ‘2020’,’03’, ‘Agency’, ‘Local’, ‘Local Standard’, ‘Medical’, ”, ‘106422’, 250.00, 250.00, 0.00, 0.00, 0.00, 0.00
‘goal’, ‘Organization Name1’, ‘Booking Company Name1’, ‘Salesrep Name1’, ”, ‘2020’,’03’, ‘Agency’, ”, ”, ”, ”, ”, 0.00, 0.00, 0.00, 0.00, 0.00, 1000.00
‘pipeline’, ‘Organization Name1’, ‘Booking Company Name1’, ‘Salesrep Name1’, ”, ‘2020’,’03’, ‘Agency’, ‘Local’, ‘Local Standard’, ‘Medical’, ’00ff332k3h58328′, ”, 0.00, 0.00, 0.00, 500.00, 500.00, 0.00
 
Is this the best way to illustrate our data to feed to flex? Ideally we would show (by common dimensions) each salesreps booked revenue compared to their current pipeline compared to their goals by product_channel. 
 
We would use the same dimensions with metrics that are connected to a record type and I think the approach is to define a slice with a filter + uniqueName on the slice. 
Is this a good approach? or is there another way that you suggest? Again all rows will have the same columns, just which metrics have 0’s or not depend on the record_type. 
 
Thank you so much,
 
Jason
 
 
 

1 answer

Public
Mykhailo Halaida Mykhailo Halaida Flexmonster March 10, 2020

Hi Jason,
 
Thank you for posting your question and sharing your thoughts.
 
Taking into account the provided information, it seems to us that the current data arrangement is optimal for your use case. 
 
We’ve prepared a quick JSFiddle example to illustrate a potential approach to configure your slice (we’ve converted the provided CSV data to JSON format for convenience): https://jsfiddle.net/flexmonster/vry2o8qn/
 
Please let us know if our understanding of your desired pivot view is correct.
 
Best regards,
Mykhailo

Please login or Register to Submit Answer