Scripting Question

[xyz_1011]

hey all,

i have the following problem: I have a table with multiple millions of records. Over a period of multiple years i have one record per customer, year and month and this customer's revenue:

customer	year	month	revenue
customer_1	2019	01	100.000
customer_1	2019	02	200.000
customer_1	2019	03	150.000
customer_1	2019	04	175.000
customer_2	2019	01	120.000
customer_2	2019	02	130.000
customer_2	2019	03	110.000

The task is to identify each customer and year in which a given customer has a revenue below an amount X (i.e. 150.000) in every single month of a given year. Following the minimum revenue of 150.000 customer_2 would be identified for the year 2019.

Is there an elegant way to do some ground work in  the script to later on flag these customers (in a pivot table, where i have year (as row) and month (as column) as dimensions) ?

Thanks a lot!
x

[Anil_Babu_Samineni]

Perhaps this?

Sum(Aggr(Sum(TOTAL <year> revenue), Month))

[xyz_1011]

Thanks a lot for your fast reply! 🙂

I would rather have something in the script done, like a new field that indicates for a customer and year whether the customer meets the 150.000 in every month of the year (or not)...any ideas how this could be done ?

[Anil_Babu_Samineni]

Hi,

It's easy way that to do like

Left Join(Main table)

Load Month, Year, Sum(revenue) as revenue, 1 as Flag

Resident [Main table]

Group by Month, Year;

[rwunderlich]

You could do something like this:

Data:
LOAD customer,
     year,
     month,
     revenue
FROM
[https://community.qlik.com/t5/App-Development/Scripting-Question/td-p/1907637]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

RevenueFlag:
Load
  customer,
  year,
  -(max(revenue) < 150) as Flag_BelowTarget
Resident Data
Group By customer, year
; 

Note this example leaves you with a valid synthetic key. I didn't deal with removing it because I didn't know where you want the flag to land in the end. But hopefully you can adapt to your actual model.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com