Being still relatively new to Qlik Sense, I am not sure how to begin approaching this problem in Qlik Sense so hoping the community can assist.
I have a sales fact table that has all the transactions and within this I want to include deductions that take sales from gross all the way to EBITDA. These deductions are rebates, discounts, variable selling & distribution & operating expenses added as extra columns. The criteria for each is as follows:
Rebates - These are only application to certain customers and can change from month to month.
Discounts - Similar to rebates but customers differ.
Variable selling & distribution - Set % for all customers but changes monthly.
Operating expenses - Set % for all customers but changes monthly
I was thinking of doing an Excel sheet that has tabs for each of the categories above which contains a list of customers with % rates by month in the columns. This is for rebates and discounts.
For the others, these will just be a percentage by month for all customers. Attached is an example of the Excel file.
My questions is, is my idea of an excel file the best solution and if so, how to apply it to the load script.
Hi, I think that using an excel for this data is ok. Note that probably you wil need a file for each yoear or add columns for past and future years,
You can red this excel using Crosstable, for rebates and discounts use a mpping table with a composite key by Customer-Period to get the % to apply. For S&D and OPEX the mapping table can be just by period.