Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

malradi88
Contributor

Count if Function (Income Increase)

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


8 Replies
Khan_Mohammed
Honored Contributor II

Re: Count if Function (Income Increase)

Can you provide a sample XL data file  or app?

malradi88
Contributor

Re: Count if Function (Income Increase)

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!

Khan_Mohammed
Honored Contributor II

Re: Count if Function (Income Increase)

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?

Khan_Mohammed
Honored Contributor II

Re: Count if Function (Income Increase)

2018-07-31_132625.png

Anyway by using your expression I get this...

Not sure if the figures are correct.

malradi88
Contributor

Re: Count if Function (Income Increase)

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

malradi88
Contributor

Re: Count if Function (Income Increase)

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


Khan_Mohammed
Honored Contributor II

Re: Count if Function (Income Increase)

In your data you just have Monthly Income

Do you have a field with Previous Income?

How do I get previous or new income?

Khan_Mohammed
Honored Contributor II

Re: Count if Function (Income Increase)

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.

Community Browser