How to create histogram in Studio View

Dear all,

I would like to share how to create histogram in Studio View.

First of all, knowledge model is updated for bucket (x axis) and count (y axis). In my example SAP sales order item count is the bucket.

records:
  - id: salesOrderRecord
    displayName: Sales Order Record
    identifier:
      id: salesOrder
      displayName: Sales Order
      pql: VBAK.MANDT||VBAK.VBELN
    attributes:
      - id: salesOrderCount
        displayName: Sales Order Count
        pql: COUNT_TABLE(VBAK)
      - id: salesOrderItemCountBucket
        displayName: Sales Order Item Count Bucket
        pql: |
          CASE 
          WHEN PU_COUNT(VBAK,VBAP.MANDT) = 1 THEN '1'
          WHEN PU_COUNT(VBAK,VBAP.MANDT) > 1 AND PU_COUNT(VBAK,VBAP.MANDT) <= 5 THEN '2-5'
          WHEN PU_COUNT(VBAK,VBAP.MANDT) > 5 THEN '>5'
          ELSE NULL END

Then Create View Component using above attributes.

components:
- id: "histogram"
  type: "chart"
  settings:
    interactions:
      selection: true
    name: "Histogram of line item"
    encodings:
      x: "salesOrderItemCountBucket"
    visuals:
    - mark: "bar"
      encodings:
        y:
          field: "salesOrderCount"
          axis:
            position: "left"

Finally assign component to layout as standard way, then I can show you the histogram as below capture.

Best regards,
Kazuhiko

7 Likes

My previous Knowledge model has fixed buckets and not flexible when data is filtered by another dimension (e.g. company code).

To become flexible bucket, salesOrderItemCountBucket attribute PQL is changed to below. I setup 3 buckets.

      CASE 
      WHEN PU_COUNT(VBAK,VBAP.MANDT) <= FLOOR((1/3)*(GLOBAL(MAX(PU_COUNT(VBAK,VBAP.MANDT))) + (2/3)*GLOBAL(MIN(PU_COUNT(VBAK,VBAP.MANDT))))) THEN 
        '1. '||GLOBAL(MIN(PU_COUNT(VBAK,VBAP.MANDT)))||'-'||FLOOR((1/3)*(GLOBAL(MAX(PU_COUNT(VBAK,VBAP.MANDT))) + (2/3)*GLOBAL(MIN(PU_COUNT(VBAK,VBAP.MANDT)))))
      WHEN PU_COUNT(VBAK,VBAP.MANDT) <= FLOOR((2/3)*(GLOBAL(MAX(PU_COUNT(VBAK,VBAP.MANDT))) + (1/3)*GLOBAL(MIN(PU_COUNT(VBAK,VBAP.MANDT))))) THEN 
        '2. '||FLOOR((1/3)*(GLOBAL(MAX(PU_COUNT(VBAK,VBAP.MANDT))) + (2/3)*GLOBAL(MIN(PU_COUNT(VBAK,VBAP.MANDT)))))||'-'||FLOOR((2/3)*(GLOBAL(MAX(PU_COUNT(VBAK,VBAP.MANDT))) + (1/3)*GLOBAL(MIN(PU_COUNT(VBAK,VBAP.MANDT)))))
      ELSE
        '3. '||FLOOR((2/3)*(GLOBAL(MAX(PU_COUNT(VBAK,VBAP.MANDT))) + (1/3)*GLOBAL(MIN(PU_COUNT(VBAK,VBAP.MANDT)))))||'-'||GLOBAL(MAX(PU_COUNT(VBAK,VBAP.MANDT)))
      END 

The point is to use GLOBAL function against MAX/MIN of x axis value. It enables us to calculate global value like static value variable in Analysis.

1 Like