Bar Chart creation issue with calculated field

Question & Answer
, ,
1

I have an order item report and I want to create a bar chart to visualize the number of products sold in different quantities. Specifically, I want to show the number of products sold as:

  • 1 unit
  • 2 units
  • 3 units
  • 4 units
  • 5 units
  • More than 5 units (combined)

For example, if a total of 100 products were sold:

  • 40 products were sold as single units (1 unit each)
  • 20 products were sold in orders of 4 units (which could be 4 separate orders with 1 unit each)

However, I’m having trouble creating this chart. Can anyone advise on how to achieve this visualization?

2

Hi @Subhash_Malvi ,

To understand your requirement correctly, have a quick question.

Since your data is at order item level, for an item I1 in order O1 the quantity is 2 units however the same I1 has 2 orders O3 and O4 of 5 units each. How do you want to classify the product here? Do you want to consider weighted average to compute units sold per product and then classify in groups?

Thanks,
Prantika

3

My question is about analyzing product sales quantities in my order item report, which includes data for multiple stores. Here’s the scenario:

  • I have a report with data for different stores (Store A, Store B, etc.).
  • Within a specific timeframe (day or month), I want to understand how many products were sold in various quantities, regardless of individual orders.
  • For example, let’s say product X in Store A sold a total of 5 units during a particular month.

I’m not interested in how those units were sold individually (one order or multiple). I just want to know that a total of 5 units were sold for product X in that timeframe.

Ideally, I want to create a bar chart with these categories:

  • 1 unit sold
  • 2 units sold
  • 3 units sold
  • 4 units sold
  • 5 units sold
  • (Optional: More than 5 units sold)

This would show the total number of products sold for each quantity within the chosen timeframe, regardless of how many individual orders contributed to that total.

4

I tried to create a calculated field, but I encountered an issue with using an aggregated field on the x-axis.
Yesterday Sold Qty : sumIf(Quantity,(Status=“Delivered” AND dateDiff(createdat,now(),‘DD’)=1))

SKU Bin for Yesterday Sales.

ifelse(

{Yesterday Sold Qty} >= 0 AND {Yesterday Sold Qty} < 1,“0 - 1 Unit (Gram)”,

{Yesterday Sold Qty} >= 1 AND {Yesterday Sold Qty} < 2,“1 Unit”,

{Yesterday Sold Qty} >= 2 AND {Yesterday Sold Qty} < 3,“2 Unit”,

{Yesterday Sold Qty} >= 3 AND {Yesterday Sold Qty} < 4,“3 Unit”,

{Yesterday Sold Qty} >= 4 AND {Yesterday Sold Qty} < 5,“4 Unit”,

{Yesterday Sold Qty} >= 5 AND {Yesterday Sold Qty} < 6,“5 Unit”,

“More than 5”

)

5

Not sure why the issue arrived. I assume there was more than one Bin returned against the category causing aggregation error.

I can think of one way to implement this, but would request you to create a sample dataset and report in arena for further exploration.

Below are the steps I can think of (similar to your implementation)

step 1 : calculate quantity at item level
step 2: classify bins from the quantity, thus allotting single bin to each product
step 3: bins in x axis and distinct item count in y axis of bar chart

Have tried to recreate a sample use case using a similar dataset I have. Please check this out.
31565 sample solution

6

Thanks for answering but the issue comes due to I am using aggregation (sum of Qty Sold) at item level. Due to this, the bin is not accepted in the x-axis.

7

Kindly create a sample dataset and analysis in arena so that we understand the challenge you are facing.

8

I Added Sample Dataset (Sample Data Attached )

Here, I want to know the total quantity sold for each product, organized by bin, within a specific period. I should be able to select the start and end dates.

I tried but getting error of aggregation

Example 1 - 15 June for Store A, Product xyz, total sold unit is 4 then it should be in 4 unit bin

Same if 10 unit sold then it should be in more than 5 unit.

I want to create Histogram/Bar Chart for it.

9

Please check this out (sheet2).
updated solution 31565