Hello, I am trying to filter the second table when the first table is clicked. Something like how you do it in Tableau. I have loaded the model from my SSMS.
my dashboard consists of 4 filters.
Year, Select Group. Item Structure old/new (which is based on the dynamic field) and another filter which is pieces, sale, cost (also based on the dynamic field).
def performance_efficiency_monitor_view(request):
# Filters
selected_year = request.GET.get('p_year', '2023')
selected_fair_group = request.GET.get('fair_group')
view_type = request.GET.get('view_type', 'āļāļģāļāļ§āļāļāļīāđāļ')
view_point = request.GET.get('view_point', 'old_structure')
years = TbFairMonitorActive.objects.order_by('p_year').values_list('p_year', flat=True).distinct()
fair_groups = TbFairMonitorActive.objects.order_by('fair_group').values_list('fair_group', flat=True).distinct()
filters = {}
if selected_year:
filters['p_year'] = selected_year
if selected_fair_group:
filters['fair_group'] = selected_fair_group
queryset = TbFairMonitorActive.objects.filter(**filters)
# Dynamic Fields
group_by_fields = {
'old_structure': ('dept_name', 'product_name'),
'new_structure': ('brand_group', 'new_product_name')
}
group_fields = group_by_fields.get(view_point, group_by_fields['old_structure'])
field_mappings = {
'āļĒāļāļāļāļēāļ': ('so_amtvat', 'stock_amtvat'),
'āļĒāļāļāļāļēāļ (āļĢāļēāļāļēāļāļļāļ)': ('so_cost', 'stock_cost'),
'āļāļģāļāļ§āļāļāļīāđāļ': ('so_pcs', 'stock_pcs'),
}
sales_field, stock_field = field_mappings.get(view_type, ('so_pcs', 'stock_pcs'))```
I have made the backend, which is needed to show both of the table.
First table.
dept_field, product_field = group_fields
divisions = queryset.values(dept_field).distinct().order_by(dept_field)
division_data = []
for division in divisions:
products = queryset.filter(**{dept_field: division[dept_field]}).values(dept_field, product_field).annotate(
total_so_pcs=Sum(sales_field),
stock_pcs=Sum(stock_field),
sales_stock_product_ratio=Case(
When(stock_pcs__gt=0, then=F('total_so_pcs') * 100.0 / F('stock_pcs')),
default=Value(0.0),
output_field=FloatField()
)
)
total_sales = sum(p['total_so_pcs'] for p in products)
total_stock = sum(p['stock_pcs'] for p in products)
sales_stock_ratio = (total_sales / total_stock * 100) if total_stock > 0 else 0.0
division_data.append({
'dept_field': division[dept_field],
'total_sales': total_sales,
'total_stock': total_stock,
'sales_stock_ratio': sales_stock_ratio,
'product_field': list(products),
})
Second table.
group_products = list(queryset.values('group_product').annotate(
total_so_pcs=Sum(sales_field),
total_stock_pcs=Sum(stock_field)
).order_by('group_product'))
max_sales = max((gp['total_so_pcs'] for gp in group_products), default=1)
max_stock = max((gp['total_stock_pcs'] for gp in group_products), default=1)
for gp in group_products:
gp['sales_pct'] = (gp['total_so_pcs'] / max_sales * 100) if max_sales > 0 else 0
gp['stocks_pct'] = (gp['total_stock_pcs'] / max_stock * 100) if max_stock > 0 else 0
gp['sales_stock_ratio'] = (gp['total_so_pcs'] / gp['total_stock_pcs'] * 100) if gp['total_stock_pcs'] > 0 else 0
and here is the frontend (HTML) of both of the table.
<div class="tables-container">
<!-- First Table -->
<div class="table-container" style="overflow-y: auto; max-height: 600px; float: left; width: 50%;">
<table id="first-table">
<thead>
<tr>
<th>Division</th>
<th>Product</th>
<th>āļĒāļāļāļāļēāļĒ</th>
<th>āļŠāļāđāļāļ</th>
<th>āļĒāļāļāļāļēāļĒ / āļŠāļāđāļāļ</th>
</tr>
</thead>
<tbody>
{% for division in division_data %}
{% for product in division.product_field %}
<tr class="product-row" data-product="{{ product.product_field }}" data-division="{{ division.dept_field }}">
{% if forloop.first %}
<td rowspan="{{ division.product_field|length }}">{{ product|get_item:dept_field }}</td>
{% endif %}
<td>{{ product|get_item:product_field }}</td>
<td>{{ product.total_so_pcs }}</td>
<td>{{ product.stock_pcs }}</td>
<td>{{ product.sales_stock_product_ratio|floatformat:"2" }}%</td>
</tr>
{% endfor %}
<tr class="division-total" data-division="{{ division.dept_field }}">
<td></td>
<td><strong>Total</strong></td>
<td><strong>{{ division.total_sales }}</strong></td>
<td><strong>{{ division.total_stock }}</strong></td>
<td><strong>{{ division.sales_stock_ratio|floatformat:"2" }}%</strong></td>
</tr>
{% empty %}
<tr>
<td colspan="5">No data available for the selected criteria.</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
<!-- Second Table -->
<div class="table-container" style="overflow-y: auto; max-height: 600px; float: right; width: 50%;">
<table id="second-table">
<thead>
<tr>
<th style="width: 20%;">By Group Product</th>
<th style="width: 15%;"></th>
<th style="width: 65%;"></th>
</tr>
</thead>
<tbody>
{% for product in group_products %}
<tr class="product-details" data-product="{{ product.product_field }}" data-division="{{ product.dept_field }}">
<td rowspan="2">{{ product.group_product }}</td>
<td>āļĒāļāļāļāļēāļĒ</td>
<td>
<div style="position: relative; height: 20px; width: 100%;">
<div class="horizontal-bar" style="position: absolute; top: 0; left: 0; height: 100%; background-color: #4ac6e2; width: {{ product.sales_pct }}%;">
{{ product.total_so_pcs }}
</div>
</div>
</td>
</tr>
<tr class="product-details" data-product="{{ product.product_field }}" data-division="{{ product.dept_field }}">
<td>āļŠāļāđāļāļ</td>
<td>
<div style="position: relative; height: 20px; width: 100%;">
<div class="horizontal-bar" style="position: absolute; top: 0; left: 0; height: 100%; background-color: #2ECC71; opacity: 0.7; width: {{ product.stocks_pct }}%;">
{{ product.total_stock_pcs }}
</div>
</div>
</td>
</tr>
{% empty %}
<tr>
<td colspan="3">No data available for the selected criteria.</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
What I want to do right now is that, when any division or product is clicked in the first table, the second table filters according to the relevant group of that division or product. Like Tableau!.
Sorry for my bad english.