Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlik Community,
I hope this finds you well. I am currently trying to develop an expression to measure '# of Workers Whose Monthly Income Increased by at Least 10%'
This is the expression I have for Current Income:
avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income]) + avg(aggr(sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], MonthYear))
This is the expression I have for Pre Income:
avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income])
The Identifier is:
[Identifier Code]
Usually I would insert a '(Current Income - Pre Income)/Pre Income as Income Increase' expression in the script then simply do a
Count ( {<Income Increase = {'> .1'}>}[Identifier Code])
but in this current data model I have two separate tables. The first containing worker dimensions including their monthly pre-income (this is static and does not change). The second containing the income they earn via the new job (it is a leather workshop that they work part time in so the current income would be the pre monthly income + whatever they earn from this - hence the addition in the 'Current Income' expression above) where they get paid per batch delivered. So in one month they might have four rows of data detailing the different batches they delivered and how much they got paid for it.
With this model I am struggling to create a Count(If statement work. Any suggestions or ideas would be much appreciated
Thank you for your support.
Best,
Mohammed
Can you provide a sample XL data file or app?
Dear Shahbaz,
Please find attached the sample sheet and enclosed the script:
[Dimension Sheet]:
LOAD
"Identifier Code",
"Age",
"Education Status",
"Marital Status",
Boy,
Girl,
"# of Sessions Attended",
"Training Attended",
"Monthly Income",
"Production Team Member"
FROM [xxxxxxx [Dimension Sheet]);
[Fact Production Leather Workers]:
LOAD
"Identifier Code",
"Date Produced",
"Date Produced" as date,
// "Financial Year",
"Product #" & '+' & "Batch #" & '+' & "Financial Year" as Key,
"Quantity Produced",
"Cost per Unit",
Remuneration,
'Date Produced' as Datetype,
"Payment Status"
FROM [xxxxxx [Fact Production Leather Workers]);
Temp:
Load num#([date],'#') as NumericDate,
Key
RESIDENT [Fact Production Leather Workers];
RESIDENT [Fact Order Sheet];
MinMax:
LOAD
Min(NumericDate) AS MinDate,
Max(NumericDate) AS MaxDate
RESIDENT Temp;
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
MasterCalendar1:
LOAD Distinct
Date($(vMinDate) + RecNo() - 1) AS [date],
Year(Date($(vMinDate) + RecNo() - 1)) as [Year],
Month(Date($(vMinDate) + RecNo() - 1)) as [Month],
Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MM-YYYY') AS [MonthYear],
'Q'&ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&'-'&Year(Date($(vMinDate) + RecNo() - 1)) As QuarterYear
AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);
DROP TABLE MinMax;
DROP TABLE Temp;
I have been trying to construct a count if statement along the lines of:
Count [Identifier Code] if (
avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income]) + avg(aggr(sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], MonthYear))
-
avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income])
/ avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income])
> .1
but no luck. Thank you for your support!
How are you getting the fields Pre Monthly Income and Current Income?
In your data I just have Monthly Income
Are there any dates which fall under Pre Monthly Income?
Anyway by using your expression I get this...
Not sure if the figures are correct.
Hi Shahbaz,
The Pre Monthly Income = the 'Monthly Income' field. Unfortunately there are no dates that fall under 'Monthly Income'. It is just a figure that is collected via survey at the beginning of the project.
The Current Income = 'Monthly Income' + 'Remuneration (the idea is that these people continue earning there pre-income and that this job is a source of supplementary income.
Thank you for your help
Best,
Mohammed
Dear Shahbaz,
Yes those figures seem to be correct (except I would not including Batch # in the table, rather just a KPI object or in a visualisation with date, education status, marital status as dimensions).
How would I construct an expression that counts the number of workers whose monthly income has increased by more than 10%? With the assumption that pre income is:
avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income])
and current income is:
avg({<MonthYear,QuarterYear,Year>}[Pre Monthly Income]) + avg(aggr(sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], MonthYear))
I was trying to put together some if statements but I can't seem to get the syntax right. Thank you for your support Shahbaz
Best,
Mohammed
In your data you just have Monthly Income
Do you have a field with Previous Income?
How do I get previous or new income?
Not sure if this is what you are looking for ?
From what I understood, Monthly Income + Remuneration = Current Income
Monthly Income is also Previous Income
Correct me if I'm wrong.