Data model question: report data on lower level than it is recorded

[BartVA]

I have a general data model question.

Imagine a company with 10.000 products, divided over 300 Brands which are grouped in 10 Brand Groups (9 major, and "other"). These products are sold to 50.000 Customers grouped in 12 Market Segments.

Now we want to do a time registration for all 50 Employees so we can track which payroll costs go where in terms of Products, Brands, Brand Groups, Customers and Brand Segments.

The time would always be registered on the Employee, the Product dimension (Product, Brand, Brand Group) and the Customer dimension (Customer, Market Segment). Sometimes it would be one product or one customer, like 30 minutes for Product P1000 and Customer C15, but much more often it would be higher up in the hierarchy, for instance 30 minutes for Brand Group BG3 and Market Segment MS5.

The challenge is now to make in Qlik Sense a pivot-table where we can see the costs per Brand Group and Market Segment (the highest levels), and where we can drill down (expand) to Product and Customer, even if a lot of the time registration basic data is not that granular.

We cannot make a table with per product and per customer a record (like for instance divide the time logged on Brand Group level equally over all Products and all Customers), because in real life this would lead to a humongous amount of data. What would be the strategy to achieve such a pivot table, or a report that would allow a similar functionality?

All ideas, advice, pointers, remarks would be very welcome! Thanks!

[TauseefKhan]

Hi @BartVA ,

Steps to Model the Data:
1- Load the Hierarchical Dimensions
2- Aggregate Time Registration Data
3- Build the Pivot Table in Qlik Sense

1. Properly modeling the hierarchical dimensions (Products and Customers) in separate tables.
2. Aggregating the time registration data at the appropriate levels using conditional expressions.
3. Joining the aggregated data with the hierarchical dimension tables to enable drill-down functionality.
4. Cleaning up the intermediate tables to optimize performance.
   
   ***Hope this resolve your issue.
   If the issue is solved please mark the answer with Accept as Solution & like it.***

[marcus_sommer · Accepted solution]

A humongous amount of data is quite relative - an relates always to the available resources and wanted views ...

If a certain granularity isn't loaded you won't be able to show them. Instead loading the complete origin data you may populate the lowest granularity by dividing a higher granularity to their distinct items. Before going in this direction you should consider to load all origin data with an attention to the distinct number of field-values, for example by distributing timestamps into dates and times - and maybe the responsiveness is further acceptable.

[BartVA]

Thanks for the clarification!

[BartVA]

@marcus_sommer wrote:

Instead [of] loading the complete origin data you may populate the lowest granularity by dividing a higher granularity to their distinct items.

---

Extra question about this. Can this populating be done "on the fly" in a visualization? I am under the impression that that is impossible, what do you think? I mean, you can easily aggregate granular data into a less granular form (sum, average, ...) in a visualization. The opposite however, dividing less granular data on the fly into more granular data, seems not possible to me? Or am I mistaken?

[marcus_sommer]

Yes, but it's not really recommended - because all calculations require always a dimensionally context respectively that the wanted dimensions + dimension-vaues must exists. This could be done within the UI with calculated dimensions, like (simple examples):

if(match(MyDim, 'x', 'y', 'z'). MyDim, 'Sonstige')

aggr(if(rank(MyExpression) <= 10, MyDim, 'Sonstige'), MyDim)

but such approaches have more or less disadvantages in regard to the performance and usability and should be avoided if any possible.

Of course more complex things like a data-population are technically within the UI possible but the needed efforts and know how will be much bigger as doing all essential stuff within the data-model.