Multiple Supplier Rebate Thresholds

[tha5002]

Hi all,

New job and new to qlik sense! I was given a project to become familiar with qlik, and have run into some knowledge/experience roadblocks. I would really appreciate any help that can be provided.

I am trying to create an app that will provide information related to supplier spend, and the rebate we expect to receive based on thresholds.

Similar to https://community.qlik.com/t5/QlikView-App-Dev/Help-With-Complex-quot-If-ElseIF-ElseIF-quot-Statemen...

I believe there are more complexities required for my app. I will try to give as much context as possible.

In order to accommodate changing agreements with suppliers, the thresholds and rebate % will be maintained in an excel document that will be loaded as needed. 

As you can see from the sample screenshot, there are different rebate collection periods, and rebate types.

For the time being I am most concerned with 'Quarterly' and 'Yearly' Rebate Collection Period, and the 'Threshold' Rebate Type.

Quarterly = The threshold is calculated based on the total amount of PREVIOUS YEAR SPEND. (Rebate paid quarterly)

Example: Supplier 4 spent $12,000,000 in 2021, effective rebate for any 2022 spend should be 5%

If ([Rebate Collection Period] = 'Quarterly', Sum({<Year= {'$(=Max(Year)-1)'}>} [Total PO Spend (USD)])) -- Used this to check PY

Yearly = The threshold is calculated based on the total amount YEAR TO DATE (Rebate paid at end of year)

Example: As spend increases throughout the year, the rebate should reflect this. 

If ([Rebate Collection Period] = 'Yearly', Sum({<Year= {'$(=Max(Year))'}>} [Total PO Spend (USD)])) -- Used this to check YTD

Yearly (Mar - Feb) etc... will be for suppliers that use a different fiscal year. I will tackle that after this portion works...

A user will select the 'reporting year' (e.g. 2022), and the output should display rebate values for monthly, quarterly, total. Image below.

I used the following expression to try and find the correct rebate. I believe that it is only choosing the lowest rebate threshold, and not going through the entire statement, as Supplier 4 in above image should be a higher tier.

IF ([Rebate Collection Period] = 'Yearly',

If(Sum({<Year = {'$(=Max(Year))'}>} [Total PO Spend (USD)] >= 1 and [Total PO Spend (USD)] <= [Spend Threshold]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate),

If(Sum({<Year = {'$(=Max(Year))'}>} [Total PO Spend (USD)] > [Spend Threshold] and [Total PO Spend (USD)] <= [Spend Threshold2]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate2),

If(Sum({<Year = {'$(=Max(Year))'}>} [Total PO Spend (USD)] > [Spend Threshold2] and [Total PO Spend (USD)] <= [Spend Threshold3]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate3),

If(Sum({<Year = {'$(=Max(Year))'}>} [Total PO Spend (USD)] > [Spend Threshold3] and [Total PO Spend (USD)] <= [Spend Threshold4]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate4),

If(Sum({<Year = {'$(=Max(Year))'}>} [Total PO Spend (USD)] > [Spend Threshold4] and [Total PO Spend (USD)] <= [Spend Threshold5]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate5),

If(Sum({<Year = {'$(=Max(Year))'}>} [Total PO Spend (USD)] > [Spend Threshold5] and [Total PO Spend (USD)] <= [Spend Threshold6]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate6),

)))))),

If ([Rebate Collection Period] = 'Quarterly',

If (Sum({<Year = {'$(=Max(Year)-1)'}>} [Total PO Spend (USD)] >= 1 and [Total PO Spend (USD)] <= [Spend Threshold]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate),

If (Sum({<Year = {'$(=Max(Year)-1)'}>} [Total PO Spend (USD)] > [Spend Threshold] and [Total PO Spend (USD)] <= [Spend Threshold2]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate2),

If (Sum({<Year = {'$(=Max(Year)-1)'}>} [Total PO Spend (USD)] > [Spend Threshold2] and [Total PO Spend (USD)] <= [Spend Threshold3]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate3),

If (Sum({<Year = {'$(=Max(Year)-1)'}>} [Total PO Spend (USD)] > [Spend Threshold3] and [Total PO Spend (USD)] <= [Spend Threshold4]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate4),

If (Sum({<Year = {'$(=Max(Year)-1)'}>} [Total PO Spend (USD)] > [Spend Threshold4] and [Total PO Spend (USD)] <= [Spend Threshold5]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate5),

If (Sum({<Year = {'$(=Max(Year)-1)'}>} [Total PO Spend (USD)] > [Spend Threshold5] and [Total PO Spend (USD)] <= [Spend Threshold6]), Sum({<Month={'Jan'}>}[Total PO Spend (USD)] * Rebate6),

))))))))

I am not sure if there is a simpler way to do this, or if I just need to include something else.

Thank you for your help!