Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following data. I would like to plot 100% stacked bar chart based on the earliest sale date with whether the product has been sold within a month or not. As you can see, the product can be sold in multiple times in various dates. I would like only to use the earliest first sale date to plot bar chart. How could I get a 100% stacked bar chart by earliest sale date of its year and month?
| Product type | First_sale_time | first_sale_withinmonth | on_shelf_date | first_sale_date |
| Product6065 | 1 | Within a month | 01/04/2025 | 02/04/2025 |
| Product6065 | 24 | Within a month | 01/04/2025 | 25/04/2025 |
| Product6065 | 01/04/2025 | |||
| Product6066 | 93 | More than a month | 18/12/2024 | 21/03/2025 |
| Product6066 | 18/12/2024 | |||
| Product6070 | 49 | More than a month | 09/12/2024 | 27/01/2025 |
| Product6070 | 09/12/2024 | |||
| Product6072 | 56 | More than a month | 05/02/2025 | 02/04/2025 |
| Product6072 | 05/02/2025 | |||
| Product6073 | ||||
| Product6074 | 82 | More than a month | 10/10/2024 | 31/12/2024 |
| Product6074 | 10/10/2024 | |||
| Product6075 | 29/05/2025 | |||
| Product6079 | 15/01/2025 | |||
| Product6080 | 17/06/2025 | |||
| Product6082 | 30/12/2024 | |||
| Product6084 | 167 | More than a month | 02/04/2025 | 16/09/2025 |
| Product6084 | 02/04/2025 | |||
| Product6085 | 3 | Within a month | 26/11/2024 | 29/11/2024 |
| Product6086 | 11 | Within a month | 22/11/2024 | 03/12/2024 |
| Product6086 | 22/11/2024 | |||
| Product6087 | 23 | Within a month | 29/10/2024 | 21/11/2024 |
| Product6087 | 29/10/2024 | |||
| Product6088 | 26 | Within a month | 20/12/2024 | 15/01/2025 |
| Product6088 | 20/12/2024 | |||
| Product6089 | 13/05/2025 | |||
| Product6091 | 27 | Within a month | 06/12/2024 | 02/01/2025 |
| Product6091 | 61 | More than a month | 06/12/2024 | 05/02/2025 |
| Product6091 | 06/12/2024 | |||
| Product6092 | 6 | Within a month | 14/02/2025 | 20/02/2025 |
| Product6092 | 14/02/2025 | |||
| Product6093 | 0 | Within a month | 12/01/2025 | 12/01/2025 |
| Product6093 | 12/01/2025 | |||
| Product6094 | 1 | Within a month | 12/02/2025 | 13/02/2025 |
| Product6094 | 12/02/2025 | |||
| Product6095 | 91 | More than a month | 18/03/2025 | 17/06/2025 |
| Product6095 | 18/03/2025 | |||
| Product6096 | ||||
| Product6097 | 37 | More than a month | 14/01/2025 | 20/02/2025 |
| Product6098 | 5 | Within a month | 05/06/2025 | 10/06/2025 |
| Product6098 | 05/06/2025 | |||
| Product6099 | 13/08/2025 | |||
| Product6100 | 08/04/2025 | |||
| Product6101 | ||||
| Product6102 | 26 | Within a month | 19/03/2025 | 14/04/2025 |
| Product6103 | 29 | Within a month | 05/02/2025 | 06/03/2025 |
| Product6103 | 05/02/2025 | |||
| Product6105 | ||||
| Product6106 | 111 | More than a month | 09/05/2025 | 28/08/2025 |
| Product6106 | 09/05/2025 | |||
| Product6107 | 7 | Within a month | 03/03/2025 | 10/03/2025 |
| Product6107 | 03/03/2025 | |||
| Product6108 | 153 | More than a month | 10/07/2025 | 10/12/2025 |
| Product6108 | 10/07/2025 | |||
| Product6109 | 19/12/2025 | |||
| Product6111 | 0 | Within a month | 27/03/2025 | 27/03/2025 |
| Product6111 | 27/03/2025 | |||
| Product6112 | 18 | Within a month | 24/01/2025 | 11/02/2025 |
| Product6113 | 19 | Within a month | 17/07/2025 | 05/08/2025 |
| Product6115 | 62 | More than a month | 06/02/2025 | 09/04/2025 |
| Product6115 | 06/02/2025 | |||
| Product6116 | 14/04/2025 | |||
| Product6118 | 99 | More than a month | 10/02/2025 | 20/05/2025 |
| Product6118 | 10/02/2025 | |||
| Product6119 | 17/02/2025 | |||
| Product6120 | 15 | Within a month | 28/01/2025 | 12/02/2025 |
| Product6120 | 51 | More than a month | 28/01/2025 | 20/03/2025 |
The 100% stacked bar chart I would like to show as below:
How can I draw X axis with the earliest first sale for each product. Apparently X axis is the dimension. I couldnt put Min([first_sale_date.autoCalendar.YearMonth]) as X axis.
Many thanks
Accepted Solutions
Hi @Mini_Elaine
I'm not sure if I'm fully understanding your request. For example, for Product6091 you have two first dates in two different months and you want to always use the first of this two dates, right?
I used the following code in the backend:
Data:
LOAD Distinct
"Product type",
(Month(Min(first_sale_date)) &'-'& Year(Min(first_sale_date))) as first_sale_date
FROM [lib://DataFiles/Product Sales.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE len(first_sale_date)>0
GROUP BY "Product type";
RIGHT JOIN(Data)
LOAD
"Product type",
First_sale_time,
first_sale_withinmonth,
on_shelf_date
FROM [lib://DataFiles/Product Sales.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE len(first_sale_date)>0
;
First I take the minimum date for each product and I later join it to the main data. In this way, for this product I obtain the stacked data with the X axis as the minimum date.
Let me know if it works for you. If not, could you, please, clarify a little bit the request?
Kind Regards
Daniel
Hi @Mini_Elaine
I'm not sure if I'm fully understanding your request. For example, for Product6091 you have two first dates in two different months and you want to always use the first of this two dates, right?
I used the following code in the backend:
Data:
LOAD Distinct
"Product type",
(Month(Min(first_sale_date)) &'-'& Year(Min(first_sale_date))) as first_sale_date
FROM [lib://DataFiles/Product Sales.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE len(first_sale_date)>0
GROUP BY "Product type";
RIGHT JOIN(Data)
LOAD
"Product type",
First_sale_time,
first_sale_withinmonth,
on_shelf_date
FROM [lib://DataFiles/Product Sales.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE len(first_sale_date)>0
;
First I take the minimum date for each product and I later join it to the main data. In this way, for this product I obtain the stacked data with the X axis as the minimum date.
Let me know if it works for you. If not, could you, please, clarify a little bit the request?
Kind Regards
Daniel
