Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Ken_Hardwick
Contributor III
Contributor III

Applying rebates, discounts and other percentages

Hello,

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.

Thanks

Labels (1)
1 Reply
rubenmarin

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.