topic Customer Rewards by Range Sales in QlikView

Customer Rewards by Range Sales

lucarizziero — Thu, 02 Jul 2009 19:48:50 GMT

Hi all,

I need to assign to my customers a reward based on the range of their sales.

[Sales table]: sales date, customer, sales amount

[Reward table]: sales from, sales to, reward amount

I'd need to obtain a table containing:

customer - current year sales amount - reward amount

I mean it should be done at running time, not when loading data !

Thanks you all

Customer Rewards by Range Sales

Thu, 02 Jul 2009 21:07:25 GMT

Here's a little something that may work. I put in the data you mentioned. I wasn't sure what Reward Amount was. I added it in as a percentage of sales.

I then created a table with Customer as the Dimension. Sum(Sales) as an expression and another expression to calculate Reward.

=Sum(If(Date >= Start AND Date <= End, Sales * Reward, 0))

Then wherever the Sales Date falls in the reward ranges, that Reward amount will be used. The If is not the prettiest thing in the world, but it seems to work (if I'm understanding you correctly).

I've attached a sample (QlikView 9).

Customer Rewards by Range Sales

lucarizziero — Thu, 02 Jul 2009 21:24:07 GMT

I try to explain better.

The [sales from] and [sales to] represent the interval to which sum(sales amount) must agree for obtaining the reward.

For example:

for all customer to which I have a [sales amount] more than 10,000€ and less than 20,000€ I assign a reward of 500€

for all customer to which I have a [sales amount] more than 20,000€ and less than 30,000€ I assign a reward of 1,000€

for all customer to which I have a [sales amount] more than 30,000€ I assign a reward of 1,500€

and so on

The final table should be something like:

Customer A - [Sales amount] 500€ - [reward] 0€

Customer B - [Sales amount] 16,000€ - [reward] 500€

Customer A - [Sales amount] 50,000€ - [reward] 1,500€

and so on.

Thanks for your help

Customer Rewards by Range Sales

Thu, 02 Jul 2009 22:05:08 GMT

Yeah, that seems a bit more difficult.

I came up with something, but I'm not too thrilled with it. First, I needed to create the range for Zero Reward. Then I used Customer and Reward as dimensions. For the expression, I pulled Sum(Sales) if it was within the threshold. Otherwise, it resulted in zero. Since zero values are suppresses, those records won't show up. In the end, it looks like what you describe. Unfortunately, since the Reward is a dimension, it is on the left of Sum(Sales).

Hopefully, someone can come up with a better solution.

Customer Rewards by Range Sales

johnw — Fri, 03 Jul 2009 02:27:39 GMT

Here's another approach:

max(aggr(if(sum({<YTD={1}>}Sales)>=Min,Reward),Customer,Min))

The simplified range check doesn't required your reward table to have a 0 min or an "infinite" max. It just requires that the rewards be ascending, as any good reward structure is likely to be. And as desired, the reward is an expression rather than a dimension.