Need help with Data Association

[Aasir]

I have below tables

1. Sales table - fields: date, store name, item no, amount

2. All store budget - fields: service ID, service, months (Jul22-Jun23), amount

3. store budget - fields: service ID, store name, service, months (Jul22-Jun23), amount

count of services in All store budget is 23,  22 of this listed with each store name and only amount is distributed.

4. Mapping table - fields: item no, service ID, service category

I want 3 filters: store name, service category, month

I want help with data modeling (data manager)

[Scotchy]

Try this approach, let me know if this fulfills the requirement ...

Understanding Your Data and Relationships:

1. • Sales Table: Contains transactional data.
   • All Store Budget: Has budget information for all stores combined.
   • Store Budget: Specific budget data for each store.
   • Mapping Table: Links items to services and their categories.

2. Creating Relationships:

   • Sales to Store Budget: Link these using store name. This allows analysis of sales against store budgets.
   • Mapping Table to Sales: Link item no from the Sales table to the Mapping table. This connects sales data with service categories.
   • Mapping Table to Store Budget: Use service ID to link these. It will enable analysis of budget allocation per service category.

3. Data Modeling Steps in Qlik Sense's Data Manager:

   • Load Data: Import your tables into Qlik Sense.
   • Create Associations: Use the 'Associations' view to link your tables. Drag fields to create associations (e.g., store name in Sales and Store Budget tables).
   • Handling Date and Month: Ensure the date field in the Sales table and months in the budget tables are in a consistent format. You might need to create a calendar table for better time analysis.
   • Composite Key for Budget Tables: Since your budget tables have service ID, service, and months, consider creating a composite key (concatenation of these fields) if you need to link these tables uniquely.
   • Handling Service Categories: Ensure the service categories in the Mapping table are comprehensive and align with your analysis needs.

4. Implementing Filters:

   • Filters for Store Name, Service Category, Month: Once your data model is set up, you can create filters in your dashboard. Use list boxes or filter panes and select store name, service category, and month as dimensions.

5. Best Practices:

   • Data Quality Check: Make sure all tables are clean and consistent in terms of naming conventions and formats.
   • Performance Considerations: Be mindful of data volume. Large datasets may require optimization techniques like aggregating data at a higher level.
   • Iterative Approach: Start with a basic model and iteratively refine it based on your analysis needs and user feedback.

[Aasir]

Thanks for the detailed breakdown, I have one service that is in All store budget but not in Store budget

That can be from any store. That should be the reason to have All Store budget table.

I'm confused there.

[Scotchy · Accepted solution]

It's common in business intelligence scenarios to encounter situations where certain data elements appear in one dataset but not in another. In your case, the presence of a service in the "All Store Budget" but not in the individual "Store Budget" can indeed be a deliberate design decision, especially in a retail context. Here's how to understand and possibly manage this situation:

Understanding the Data Structure

1. Purpose of 'All Store Budget' vs. 'Store Budget':

   • All Store Budget: This table likely represents a consolidated view of budget allocations covering all stores. It might include services that are common across multiple stores or general overhead costs not attributed to a single store.
   • Store Budget: This table is more granular, focusing on budget allocations for specific stores. It might exclude certain services that are not directly attributed to individual stores but are part of the overall operational cost.

2. Why Some Services Might Not Appear in 'Store Budget':

   • Centralized Services or Costs: Some services might be managed at a central level and not allocated to individual stores (e.g., a centralized marketing campaign).
   • Data Collection Methods: The methodology for collecting budget data might differ. Some services could be overlooked or deliberately excluded at the store level.
   • Reporting Purposes: The organization might have different reporting requirements for individual stores versus the entire network.

Handling This in Qlik Sense

1. Data Modeling:

   • Recognize the unique role of each table. When creating associations in Qlik Sense, understand that the "All Store Budget" offers a broader perspective.
   • You might need to create separate visualizations or calculations for data that only exists in the "All Store Budget".

2. Composite Key Consideration:

   • If a service is not in the "Store Budget", ensure your composite key or linking mechanism in Qlik Sense doesn’t inadvertently exclude or misrepresent data.

3. Data Validation:

   • Double-check to ensure that this discrepancy isn't due to a data quality issue. It's possible that the service should be in the "Store Budget" but was omitted due to an error.