topic Scripting Question in App Development

Scripting Question

xyz_1011 — Mon, 21 Mar 2022 13:49:20 GMT

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

Re: Scripting Question

Anil_Babu_Samineni — Mon, 21 Mar 2022 13:54:54 GMT

Perhaps this?

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

Re: Scripting Question

xyz_1011 — Mon, 21 Mar 2022 15:02:35 GMT

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 ?

Re: Scripting Question

Anil_Babu_Samineni — Mon, 21 Mar 2022 15:34:01 GMT

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;

Re: Scripting Question

rwunderlich — Mon, 21 Mar 2022 23:24:41 GMT

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