🍉 Meet Flexmonster Pivot Table & Charts 2.9.Check out all hot features!
Get Free Trial

Columns with diffrent measures

Answered
Jarek Kotowski asked on December 1, 2020

Hi,
I wondering if such a report is possible to do with flexmonster:
 
https://drive.google.com/file/d/16U0j2kB4GO2l6jwJK4vvr5q5CtoIR9OP/view?usp=sharing

6 answers

Public
Dmytro Zvazhii Dmytro Zvazhii Flexmonster December 2, 2020

Hello Jarek,
Thank you for writing to us.
We have checked your screenshot, and it helped us to get a general idea of your case.
 
Yes, it is possible to get a similar report in Flexmonster.
Also, we noticed additional data representation feature, which shows some percentage of data near the main value. If we understood it correctly, for “All” column, it is some sort of percent difference, and for other columns, it is a percent of row total.
When using Flexmonster, you can achieve such sort of visualisation with the help of additional customization options such as customizeCell hook.
 
Still, the nature of some columns is not clear to us. Could you please share with us some additional information such as formulas for calculating columns and your data structure? That would help us to provide more specific steps of how to implement such a report.
You are welcome to write to our e-mail if you need to share some sensitive data.
 
Waiting to hear from you.
Regards,
Dmytro

Public
Jarek Kotowski December 13, 2020

Hi Dymytro,
Sorry for the late reply but I needed some time to collect some answers.

Here is example data structure:
{
    “order_id” : 3672,
     “order_date” : “2020-08-01”,
     “order_status” : “SEND”,
     “in_process” : 0,
     “confirmed” : 1,
     “by_phone” : 0,
     “cals_count” : 31,
     “order_price” : 300.00
},
Explenation of calculation for columns:
Leads All – total number of orders on a given day, the second value indicates by how much the number of orders has changed compared to the previous day in percent,
Leads Expect – number of orders with status: PROCESSING, RECALL, NO_ANSWER on a given day, or field in_process equals 1,
Leads Confirm – number of orders with status: TO_SEND, SEND, RETURN, DELIVERED, AFTER_RETURN, FINISHED, CONTRACT_OUT, CARRIER_RETURN, COMPLAINT, PACKED on a given day, or filed confirmed equals 1,
Leads Phone – number of telephone orders, when field by_phone equals 1
Leads Reject – number of orders with CANCEL status on a given day
Leads Fail – number of orders with FAIL status on a given day – NOT NEEDED
Leads Trash – number of orders with TRASH status on a given day
Leads No answer – number of orders with NO_ANSWER status on a given day
Leads Avg. call count – average number of connections with the customer of order on a given day

Coefficents Net approve % = 100 * Leads Confirm / (Leads Confirm + Leads Reject)
Coefficents Grosss approve % = 100 * Leads Confirm / All
Finances Total – the total value of orders on a given day
Finances Expect – the total value of orders with status: PROCESSING, RECALL, NO_ANSWER on a given day
Finances Confirm – the total value of orders counted in column “Leads Confirm”
Finances EPL-a, %- = Finances Confirm / Leads Confirm, second value specify the difference from the previous day (in percent)
Finances Reject – the total value of orders with CANCEL status on a given day
Finances Trash – the total value of orders with TRASH status on a given day
additional value in cell it is a percent of total orders but you explained that this can be achieved with customizeCell hook. Maybe I’ll need help how to get total number of orders.
Here is json data with example data:
https://drive.google.com/file/d/1nOwUuwMNpVtd-U073P5aAXLu7Nxut5Vu/view?usp=sharing
JK
 

Public
Vera Didenko Vera Didenko Flexmonster December 15, 2020

Hello, Jarek,
 
Thank you for providing further details regarding your use case. 
 
The detailed descriptions and the sample data set helped us to understand the situation better.
Our team is currently working on your case.
We will make sure to reach out to you shortly.
 
Kind regards,
Vera

Public
Vera Didenko Vera Didenko Flexmonster December 16, 2020

Hello, Jarek,
 
Thank you for giving us some time. 
 
Using the descriptions and the provided data, we were able to achieve all the calculations. 
We have prepared a code sample for reference (please see "pivot.html" in the attachments). 
 
Below are the steps on how the resulting visualization was achieved: 

  1. Data preprocessing:

    We would like to kindly explain that to achieve the described calculations, the data structure needs to be adjusted. 

    Please consider preprocessing the data from: 

    {
    "order_id":3651,
    "order_date":"2020-08-01",
    "order_status":"CANCEL",
    "in_process":0,
    "confirmed":0,
    "by_phone":0,
    "cals_count":3,
    "order_price":137.00
    }

    to the following format: 

    {
    "order_id": 3651,
    "order_date": "2020-08-01",
    "order_status": "CANCEL",
    "in_process": 0,
    "confirmed": 0,
    "by_phone": 0,
    "cals_count": 3,
    "order_price": 137,
    "CANCEL": 1,
    "TRASH": 0,
    "NOT NEEDED": 0,
    "FAIL": 0,
    "PACKED": 0,
    "COMPLAINT": 0,
    "CARRIER_RETURN": 0,
    "CONTRACT_OUT": 0,
    "FINISHED": 0,
    "AFTER_RETURN": 0,
    "DELIVERED": 0,
    "RETURN": 0,
    "SEND": 0,
    "TO_SEND": 0,
    "NO_ANSWER": 0,
    "RECALL": 0,
    "PROCESSING": 0
    }

    This will make it possible to create calculations based on the order status value.
    In the provided example, the data is preprocessed during runtime.

  2. Creating the base calculations:

    The base calculations were created using calculated values.
    Please see our calculated values guide for more details: https://www.flexmonster.com/doc/calculated-values/.

  3. Adding additional (second) calculations to the grid cells:

    For adding the second values, the getData() and customizeCell() API calls were used.
    The getData() API call was used to get the needed additional calculations. 
    We used the "%differenceofcolumn" (for "All Leads" and "Finances EPL-a" columns) and the "percentofcolumn" (for the rest) aggregations for the second values.
    The customizeCell() API call was used for adding the received calculations to the grid values.

 
 
Please let us know if this works for you. 
Also, could you please let us know your company name and if you are our client (this can be shared via email)? We need this information for our records. 
 
Looking forward to your reply.
 
Kind regards, 
Vera

Attachments:
pivot.html

Public
Jarek Kotowski December 16, 2020

Hi Vera,
 
I must admit that this report look amazing :). For now We aren’t your clients. I need to make presentation with different reporting tools and Flexmonster Pivot was first choice. I have some others tools to check. But this report look very nice. Thank you for your help.
 
Jarek

Public
Vera Didenko Vera Didenko Flexmonster December 17, 2020

Hello, Jarek,
 
Thank you for your kind feedback.

We are truly happy to hear that the provided solution was helpful.
Please feel free to reach out in case of any further questions.

Kind regards,
Vera

Please login or Register to Submit Answer