Hello,
I'm trying to set up some automated alerts using Qlik View.
I want to receive an email every time the total spent for a customer is below a certain threshold.
In the table below, you can see the structure of my data.
For every customer, the threshold is given by the avg(threshold) for each customer.
This means that thresholds are : 50 for customer A, 100 for customer B and 150 for customer C.
The total spent for each customer is the sum(spent).
Supposing we are in september2017, I should receive an email stating that the spent for customer C is below threshold (25<150).
In october2017, I should receive again an email stating that the spent for customer C is below threshold (125 <150).
In few words, I want the list of customers for which the TOTAL SPENT IN THE LAST PERIOD IS BELOW THE AVERAGE THRESHOLD .
In the alerting tab I don't see any way to add dimensions, if not directly in the formula, but I don't know how to do it
Many thanks in advance,
Cheers
Date | customer | spent | threshold |
---|
09/17 | A | 50 | 50 |
09/17 | B | 150 | 100 |
09/17 | C | 25 | 150 |
10/17 | A | 20 | 50 |
10/17 | A | 40 | 50 |
10/17 | B | 75 | 100 |
10/17 | B | 30 | 100 |
10/17 | C | 100 | 150 |
10/17 | C | 10 | 150 |
10/17 | C | 15 | 150 |