I want to make an interactive dashboard on web like tableau using dajngo.

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.

Welcome @dubeyaashish !

First, there’s no need to apologize for your language skills. We’ll work through this the best we can.

Next, it doesn’t help much to describe your requirements as “like Tableau” to people who haven’t seen or used it.

Anyway, you have three basic choices here:

  • The divisions and products are url links. When a division or product is clicked, it sends a request to the server and the server generates a new page with the second table containing the desired information. (This is known as a full-page refresh.)

or

  • You have JavaScript on your page that handles the click event for a division or product. This JavaScript issues a request to the server to get just the updated data, and updates that second table with that data. (Tools such as HTMX and jQuery are very useful for cases such as this.)

or

  • You have JavaScript on the page that handles the click event for a division or product. This JavaScript updates the table directly, without requesting data from the server. (Tools such as jQuery with Datatables, React, Vue are frequently used in these situations.)

Only the first case works with a “Django-only” environment. The other two require various degrees of JavaScript be used within your site.

1 Like

Thank you for your reply!

I went with the first solution, Now it updates the existing url with another filter.