Sales vs Budget

[Belharra_team]

Hello, I need to embed a budget in my Qlik Sense app.

To explain the data model, the Data table (fact table) contains sales information. It contains a key for the company from which the sales originate and a key for the customer. Client contains information such as family, client category and many more... Thus, I can produce aggregated information such as the amount of sales by family customer, and by company, etc.

The key to my budget is made up of more than one element: 3 in customer and 2 in Company/Deposit. But I never have the details of the client code.

My first idea was to: identify the sales concerned by my key (no problem for that) and tag the sales concerned by a budget key. And then concatenate my fact table (Data) with the budget information: key + budget turnover This way I can easily compare sales against budget. But I don't have representative items of my key for visualizations. So, I planned to integrate in the sales and budget lines the key elements such as the company name and the last name (normally these elements are in the Customer and Company tables) In fact, I have them duplicated in the fact table and changing their name such as Data_family_name_budget etc. This way I can do different visualizations with any of my key elements.

But, I was asked if the app could retain the ability to filter budget information with other information from the customer table. My response was that it's impossible because I'm not getting any keys from the client on the budget lines. Common sales lines will be filtered but budget lines will not.

I have some ideas to solve this, but do you have any ideas? And do you think it's a good idea to try to do that? The analysis of a budget should not be restricted from a certain point of view?

Thanks !

[marcus_sommer]

IMO the concatenate approach of sales and budgets is the most suitable one - by harmonizing the field-names. That sales and budgets may have a different granularity is not really a problem else quite normal.

In some cases you may populate the missing data for example by distributing of monthly values to workdays and similar to other levels like customers/clients/products by using their distribution-rates in the past and with some assumptions to the lost ones and new ones. But this would be pure virtual data and may cause more confusion as to be helpful. Therefore I would be very careful to implement such a solution.

If you don't get the budgets on the same granularity as the sales you will have always 'gaps' within some views. Means selecting anything from a lower level as the budget granularity will have an impact on the budget. From my point of view that's not an error else working as intended and it should not surprise or confuse a skilled user. Beside this you may minimize the risk of any misinterpretation by providing sales- and budget-data only on views which providing not any lower level as the budgets have and using some set analysis to ignore the lower level selections within the budgets as well as the sales.

- Marcus

[Belharra_team]

Hi Marcus,

Thank you for your reply.

I agree that the best solution seems to be a fact table with mixed granularity. My main problem is that the Qlik population of users is very heterogeneous.

I created a generic key based on family customer code because the budget is set on the family, sales carry the customer code. Budget information can access all family customer information and if anyone in the app wants to filter on customer code, customer name, etc. (any information less than the family), it will lose the budget information.

My idea is to keep the table of facts with both granularities but also that Sales can also carry Budget if the association can be made. Using this I'll never lose the information, but I'm forced to use sum + aggr when it comes to the budget key in visualizations

Guerric

[marcus_sommer]

I think it's not possible to create an application in which unskilled users couldn't make any mistakes - at least not if the data-set starts to be a bit advanced (far away from real advanced or even complex logic). Therefore the attempt to make the application operator-errors free could easily end in the opposite especially if there is the assumption by the users that they couldn't make any errors - that's so simple as a light switch ...

A very wise man said ones "... make it as simple as possible but not simpler."

Nevertheless you may consider to split the application into several ones - maybe into a dashboard and report with more or less aggregated data and a full-application with all details - to target to the different user-groups. Of course it will have some overhead in regard to the administration and resource consumption but the benefits to the usability may it be worth.

- Marcus